/****** Object:  StoredProcedure [dbo].[Proc_Ta_GetEmployeeShiftDateDetails]    Script Date: 05-12-2025 09:25:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Gopinath.k
-- Create date: 16-Dec-16
-- Description:	To fetch the employee worked details based on date for C/off Entry in THDC
-- Proc_Ta_GetEmployeeShiftDateDetails '2019/08/10','787'
-- exec Proc_Ta_GetEmployeeShiftDateDetails @date=N'2025/10/10',@EmployeeCode=N'116'
-- Proc_Ta_GetEmployeeShiftDateDetails '2019/08/15', 'VO357'a
-- =============================================
ALTER PROCEDURE [dbo].[Proc_Ta_GetEmployeeShiftDateDetails]
@date nvarchar(20),
@EmployeeCode nvarchar(20)
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @TIMEFORMAT AS NVARCHAR(10)
	DECLARE @dateFormat as int	
	declare @EmployeeID as int
	declare @LeaveStatus as nvarchar(100)
	select @DATEFORMAT=DateFormat,@TIMEFORMAT=TimeFormat from [dbo].[Func_TA_GetDateTimeFormat]()
	declare @GetLevelID as int
	select @GetLevelID=EL.LevelId,@EmployeeID=E.Employee_Id from TA_Tbl_EmployeeLevel EL inner join TA_Tbl_Employee E on E.Employee_Id=EL.EmployeeId where E.Employee_Code_Id=@EmployeeCode
    DECLARE @childID INT 
    ;WITH ParentLevel AS
    (
		SELECT *, 1 AS Lvl FROM Ta_Tbl_levelDetails WHERE id = @GetLevelID
        UNION ALL
        SELECT rh.*, Lvl+1 AS Lvl FROM Ta_Tbl_levelDetails rh
        INNER JOIN ParentLevel rc ON rh.id = rc.ParentId
     )
     SELECT TOP 1 @GetLevelID=id FROM ParentLevel r ORDER BY lvl DESC
     declare @CountRecord as int
     SELECT @CountRecord = count(Employee_Id) FROM TA_Tbl_Employee 
		WHERE Leave_Sanc_Authority = Reporting_To AND Employee_Id = @EmployeeID  
	declare @OTApproval as int
	select @OTApproval=OTApproval from TA_Tbl_ApprovalSettings where CompanyId=@GetLevelID
	DECLARE @HalfDayOTMins BIGINT, @FullDayOTMins BIGINT, @TypeOfOT BIGINT,@OtText VARCHAR(MAX)
			SELECT		@TypeOfOT = OtHourWise,
						@HalfDayOTMins = DATEDIFF(MI,'00:00', ISNULL(OtMinHrs, '00:00')),
						@FullDayOTMins = DATEDIFF(MI,'00:00',ISNULL(FullDayOTMinHrs, '00:00'))
						FROM TA_Tbl_LeavePolicy LP INNER JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.LeavePolicy_Id = LP.Policy_Id
						WHERE ELP.EmployeeId = @EmployeeID AND ELP.ToDate = (SELECT TOP 1 ELP.ToDate FROM TA_Tbl_Emp_LeavePolicy ELP
						INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId
						WHERE YS.IsActive = 1 AND ELP.EmployeeId = @EmployeeID ORDER BY ELP.ToDate DESC)


	IF(@CountRecord > 0)
	BEGIN
		SET @LeaveStatus = 'Forwarded'
	END
	ELSE IF(@CountRecord = 0)
	BEGIN
		SET @LeaveStatus = 'Pending'
	END	
	if(isnull(@OTApproval,0)=0)
	begin
		SET @LeaveStatus = 'Approved'
	end

	print @CountRecord
	print @LeaveStatus

	Select p.Employee_code_id,p.Date,p.Shift_id,
	case when OutTime is not null and OutTime<>'00:00' then
		CASE	WHEN (@TIMEFORMAT)='HH:MM' THEN  CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,p.OutTime,0)), 114) 
				 ELSE substring(convert(varchar(25),convert(datetime,OutTime)),13,5)+' '+ substring(convert(varchar(25),convert(datetime,OutTime)),18,2)
		END 
	end OutTime,
	case when InTime is not null and InTime<>'00:00' then
		CASE	WHEN (@TIMEFORMAT)='HH:MM' THEN CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,p.InTime,0)), 114) 
				ELSE substring(convert(varchar(25),convert(datetime,InTime)),13,5)+' '+ substring(convert(varchar(25),convert(datetime,InTime)),18,2)
		END
	end
	InTime,
	p.NormOt,
	p.OffOt,p.HolOt,E.Employee_FirstName + case when E.Employee_MiddleName is not null then ' ' + cast(E.Employee_MiddleName as varchar(50)) else ' ' end + E.Employee_LastName AS 'Employee Name',case WHEN p.OtMins >= @HalfDayOTMins THEN 'Reached' else 'Not Reached' end as ComStatus,
	CASE when (p.NormOt!='00:00') then 'Week Day' ELSE
	CASE when (p.OffOt!='00:00') then 'Weekly Off' ELSE 
	CASE when (p.HolOt!='00:00') then 'Holiday' ELSE
	'NO OT Record Found' END END END  as  'WeekofftypeNormOt',@GetLevelID 'TopLevelID'
	,E.Employee_Id
	,LD.name as LevelName
	,p.ExtraMins
	,@LeaveStatus as 'OverTimeStatus',p.Process_Rec_ID,
	case when isnull(@HalfDayOTMins,0)=0 then 'LeavePolicyNotAssign' else 'Assign' end as LeavePolicyStatus
	from Ta_tbl_Processeddata p 
	inner join TA_Tbl_Employee E on p.Employee_Code_Id=E.SwipeCardNo
	inner join TA_Tbl_EmployeeLevel EL on EL.EmployeeId=E.Employee_Id
	inner join Ta_Tbl_levelDetails LD on LD.ID=EL.LevelId where  date = CONVERT(DATETIME,cast(@date as nvarchar(50)), 102) 
	and CompOff_Eligible=1 and OT_Eligible=1 and E.Employee_Code_Id = @EmployeeCode
	--and p.FNAttendanceCode in ('Weekly Off', 'Holiday') and p.ANAttendanceCode in ('Weekly Off', 'Holiday') --for vaighai client  
	--Select E.Employee_FirstName + case when E.Employee_MiddleName is not null then ' ' + cast(E.Employee_MiddleName as varchar(50)) else ' ' end + E.Employee_LastName AS 'Employee Name' from Ta_Tbl_employee Where Employee_code_id=@EmployeeCode
END



--- Script 2---
ALTER PROCEDURE [dbo].[Proc_TA_EmployeeCompOffOTSanction](
      @FromDate DATETIME,
      @ToDate DATETIME,
      @EmpId VARCHAR(100),
      @EmployeeName VARCHAR(50),
      @LevelID VARCHAR(8000),
      @EmployeeCategory INT,
      @EmployeeType INT,
      @UserGroupName VARCHAR(250),
      @GradeLevel VARCHAR(20),
      @Status VARCHAR(50),
      @EmpSwipeCard VARCHAR(20),
      @ParentId INT,
      @ApprovalEmpId VARCHAR(20)
)
AS
BEGIN
	set nocount on
   DECLARE @SQLQuery VARCHAR(MAX) 
   set @SQLQuery= ''
   declare @AUserGroup VARCHAR(100) 
   declare @AUserGroupType VARCHAR(100) 
   set @AUserGroup= ''
   set @AUserGroupType= ''
   DECLARE @Id INT
   DECLARE @dateFormat as int
	DECLARE @date as nvarchar(25)			
	SET @date = (SELECT DateFormat FROM TA_Tbl_DateandTimeFormat)	
	IF(@date='dd/MM/yyyy')
		BEGIN
			SET @dateFormat = 103
		END
	ELSE IF(@date='MM/dd/yyyy')
		BEGIN
			SET @dateFormat = 101
		END
	ELSE
		BEGIN
			SET @dateFormat = 106
		END
   set @Id = ISNULL(@ParentId, 0)
      SELECT @AUserGroup = ISNULL(UG.groupName, ''),@AUserGroupType=ISNULL(ReportsBasedOn,2) FROM TA_Tbl_Employee Emp
            INNER JOIN TA_Tbl_Usergroup UG ON UG.groupId = Emp.UserGroup_Id
      WHERE Employee_Id = @ApprovalEmpId
      if(@AUserGroupType='')
      begin
		set @AUserGroupType=2
	  end
      WHILE (NOT EXISTS(SELECT Id FROM Ta_Tbl_levelDetails WHERE id = @Id AND parentId = 0) AND @Id !=0)
      BEGIN
            SELECT @Id = parentId FROM Ta_Tbl_levelDetails WHERE id = @Id
      END
      declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](CAST(@ApprovalEmpId as int)) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](CAST(@ApprovalEmpId as int)) as EmpAutoId
--Temporary table's creation
      CREATE TABLE #EmpLevel(EmployeeId INT,LevelId INT,EffectiveDate DATETIME)
      CREATE TABLE #EmpDesignation(EmployeeId INT, DesignationId INT, EffectiveDate DATETIME)
      CREATE TABLE #EmpType(EmployeeId INT, TypeId INT, EffectiveDate DATETIME)
      CREATE TABLE #EmpCategory(EmployeeId INT, CategoryId INT, EffectiveDate DATETIME)
      CREATE TABLE #LevelRights(LevelId INT)
      Create table #OTHours (Process_Rec_ID int,Employee_ID int,Employee_Code_ID nvarchar(20),Date varchar(20),Employee_FirstName nvarchar(Max),[Level Name] nvarchar(Max),HrsExtra nvarchar(20),ActHrsExtra nvarchar(20),ActExtraMins int,HrsExtraIn nvarchar(20),ExtraMins int,OverTimeStatus nvarchar(10),CompOffEligible int,OTEligible int,CompOffChecked int,OTChecked int,Status nvarchar(10),TopLevelID int)
      INSERT INTO #LevelRights 
            SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID)
--Employee Level Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
      CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
      (
            [EffectiveDate] ASC
      ) ON [PRIMARY]
/****** Object:  Index [IDX_LevelId]    Script Date: 05/27/2009 16:28:18 ******/
      CREATE NONCLUSTERED INDEX [IDX_LevelId] ON [dbo].[#EmpLevel] 
      (
            [LevelId] ASC
      ) ON [PRIMARY]
/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
      CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpLevel] 
      (
            [EmployeeId] ASC
      )ON [PRIMARY]
--Employee Designation Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
      CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation] 
      (
            [EffectiveDate] ASC
      )ON [PRIMARY]
/****** Object:  Index [IDX_LevelId]    Script Date: 05/27/2009 16:28:18 ******/
      CREATE NONCLUSTERED INDEX [IDX_DesignationId] ON [dbo].[#EmpDesignation] 
      (
            [DesignationId] ASC
      ) ON [PRIMARY]
/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
      CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpDesignation] 
      (
            [EmployeeId] ASC
      ) ON [PRIMARY]
--Employee Type Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
      CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpType] 
      (
            [EffectiveDate] ASC
      ) ON [PRIMARY]
/****** Object:  Index [IDX_TypeId]    Script Date: 05/27/2009 16:28:18 ******/
      CREATE NONCLUSTERED INDEX [IDX_TypeId] ON [dbo].[#EmpType] 
      (
            [TypeId] ASC
      ) ON [PRIMARY]
/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
      CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpType] 
      (
            [EmployeeId] ASC
      )ON [PRIMARY]
--Employee Category Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
      CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpCategory] 
      (
            [EffectiveDate] ASC
      )ON [PRIMARY]
/****** Object:  Index [IDX_CategoryId]    Script Date: 05/27/2009 16:28:18 ******/
      CREATE NONCLUSTERED INDEX [IDX_CategoryId] ON [dbo].[#EmpCategory] 
      (
            [CategoryId] ASC
      ) ON [PRIMARY]
/****** Object:  Index [IDX_EmployeeId]    Script Date: 05/27/2009 16:27:55 ******/
      CREATE NONCLUSTERED INDEX [IDX_EmployeeId] ON [dbo].[#EmpCategory] 
      (
            [EmployeeId] ASC
      ) ON [PRIMARY]
--Employee Level insert in Temporary table
      INSERT INTO #EmpLevel 
            SELECT EL.EmployeeId, EL.LevelId, MAX(EL.EffectiveDate) EffectiveDate FROM TA_Tbl_EmployeeLevel EL
                  INNER JOIN TA_Tbl_Employee Emp ON EL.EmployeeId = Emp.Employee_Id       
            WHERE Emp.Isdeleted = 0 AND EL.EffectiveDate <= CONVERT(DATETIME, CAST(@ToDate AS NVARCHAR(50)), 102) AND EL.LevelId IS NOT NULL
            GROUP BY EL.EmployeeId, EL.LevelId
      INSERT INTO #EmpDesignation 
            SELECT ED.EmployeeId, ED.DesignationID, MAX(ED.EffectiveDate) EffectiveDate FROM TA_Tbl_EmployeeDesignation ED
                  INNER JOIN TA_Tbl_Employee Emp ON ED.EmployeeId = Emp.Employee_Id 
            WHERE Emp.Isdeleted = 0 AND ED.EffectiveDate <= CONVERT(DATETIME, CAST(@ToDate AS NVARCHAR(50)), 102) AND ED.DesignationId IS NOT NULL
            GROUP BY ED.EmployeeID, ED.DesignationID
      INSERT INTO #EmpType 
            SELECT ET.EmployeeId, ET.TypeId, MAX(ET.EffectiveDate) EffectiveDate FROM TA_Tbl_EmployeeType ET
                  INNER JOIN TA_Tbl_Employee Emp ON ET.EmployeeId = Emp.Employee_Id 
            WHERE Emp.Isdeleted = 0 AND ET.EffectiveDate <=CONVERT(DATETIME, CAST(@ToDate AS NVARCHAR(50)), 102) AND ET.TypeId IS NOT NULL
            GROUP BY ET.EmployeeId, ET.TypeId
      INSERT INTO #EmpCategory
            SELECT EC.EmployeeId, EC.CategoryId, MAX(EC.EffectiveDate) EffectiveDate FROM TA_Tbl_EmployeeCategory EC
                  INNER JOIN TA_Tbl_Employee Emp ON EC.EmployeeId = Emp.Employee_Id 
            WHERE Emp.Isdeleted = 0 AND EC.EffectiveDate <= CONVERT(DATETIME, CAST(@ToDate AS NVARCHAR(50)), 102) AND EC.CategoryId IS NOT NULL
            GROUP BY EC.EmployeeId, EC.CategoryId
      --CONVERT(VARCHAR(5), DATEADD(MI, ISNULL(PD.OtMins, 0), ''00:00''), 108)
      SET @SQLQuery = 'SELECT PD.Process_Rec_ID, Emp.Employee_Id, Emp.Employee_Code_Id, case when '+cast(@dateFormat as varchar(100))+'=106 then
					REPLACE(CONVERT(VARCHAR, PD.[Date],'+cast(@dateFormat as varchar(100))+'),'+''' '''+','+'''-'''+') 
					else CONVERT(VARCHAR, PD.[Date], '+cast(@dateFormat as varchar(100))+') end  AS [Date],'
      SET @SQLQuery = @SQLQuery + ' Emp.Employee_FirstName + SPACE(1) + ISNULL(Emp.Employee_LastName, '''') Employee_FirstName, REPLACE(LD.Name, ''&amp;'', ''&'') [Level Name],'
      SET @SQLQuery = @SQLQuery + ' (CASE WHEN PD.HrsOT != ''00:00'' THEN PD.HrsOT WHEN PD.NormOT != ''00:00'' THEN PD.NormOT WHEN PD.OffOT != ''00:00'' THEN PD.OffOT WHEN PD.HolOT != ''00:00'' THEN PD.HolOT END) [HrsExtra],'
      SET @SQLQuery = @SQLQuery + ' (CASE WHEN isnull(PD.ActHrsOT,PD.HrsOT) != ''00:00'' THEN isnull(PD.ActHrsOT,PD.HrsOT) WHEN isnull(PD.ActNormOT,PD.NormOT) != ''00:00'' THEN isnull(PD.ActNormOT,PD.NormOT) WHEN isnull(PD.ActOffOT,PD.OffOT) != ''00:00'' THEN isnull(PD.ActOffOT,PD.OffOT) WHEN isnull(PD.ActHolOT,PD.HolOT) != ''00:00'' THEN isnull(PD.ActHolOT,PD.HolOT) END) [ActHrsExtra],isnull(PD.ActOtMins,PD.OtMins) [ActExtraMins],'
      SET @SQLQuery = @SQLQuery + ' (CASE WHEN PD.HrsOT != ''00:00'' THEN PD.HrsOT WHEN PD.NormOT != ''00:00'' THEN ''NormOTHrs'' WHEN PD.OffOT != ''00:00'' THEN ''WeekOffOT'' WHEN PD.HolOT != ''00:00'' THEN ''HolOT'' END) [HrsExtraIn],'
      SET @SQLQuery = @SQLQuery + ' PD.OtMins [ExtraMins], 
      (CASE WHEN ((''' + @AUserGroup + ''' = ''SuperAdmin'' OR ' + @ApprovalEmpId + ' = ISNULL(Emp.Reporting_To, 0) OR ' + @AUserGroupType + ' = 0) 
      AND (ISNULL(PD.OverTimeStatus, ''Pending'') = ''Pending'') AND (''' + @AUserGroup + ''' = ''SuperAdmin'' OR ' + @ApprovalEmpId + ' = ISNULL(Emp.Leave_Sanc_Authority, 0) OR ' + @AUserGroupType + ' = 0)) 
      THEN ''Forwarded'' ELSE ISNULL(PD.OverTimeStatus, ''Pending'') END) [OverTimeStatus],'
      SET @SQLQuery = @SQLQuery + ' (CASE WHEN ((DATEADD(D, ISNULL(SS.VaildityCompOffDays, 0), PD.[Date]) < GETDATE()) OR (ISNULL(PD.OverTimeStatus, ''Pending'') = ''Approved'')) THEN 0 WHEN ((DATEDIFF(MI,''00:00'', ISNULL(SS.HalfDayCompOff, ''00:00'')) > ISNULL(PD.OtMins, 0)) OR ISNULL(PD.OtMins, 0) = 0) THEN 0 ELSE 1 END) [CompOffEligible],'
      SET @SQLQuery = @SQLQuery + ' (CASE WHEN ((ISNULL(PD.OtMins, 0) = 0) OR (ISNULL(PD.OverTimeStatus, ''Pending'') = ''Approved'')) THEN 0 ELSE 1 END) [OTEligible],'
      SET @SQLQuery = @SQLQuery + ' ISNULL(OTS.CompOffEligible, 0) [CompOffChecked], ISNULL(OTS.OTEligible, 0) [OTChecked],'
      SET @SQLQuery = @SQLQuery + ' (CASE WHEN ''' + @AUserGroup + ''' = ''SuperAdmin'' OR ' + @ApprovalEmpId + ' = ISNULL(Emp.Leave_Sanc_Authority, 0) OR ' + @AUserGroupType + ' = 0 THEN ''Approved'' WHEN ((' + @ApprovalEmpId + ' = ISNULL(Emp.Reporting_To, 0)) AND (' + @ApprovalEmpId + ' = ISNULL(Emp.Leave_Sanc_Authority, 0))) THEN ''Approved'' WHEN ' + @ApprovalEmpId + ' = ISNULL(Emp.Reporting_To, 0) THEN ''Forwarded'' ELSE ''Pending'' END) [Status],'     
      SET @SQLQuery = @SQLQuery + CAST(@Id AS VARCHAR(15)) + ' [TopLevelId]'  
      SET @SQLQuery = @SQLQuery + ' FROM TA_Tbl_ProcessedData PD'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Employee Emp ON PD.Employee_Code_Id = Emp.SwipeCardNo'
      SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.EmployeeId = Emp.Employee_Id'
      SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_LeavePolicy LP ON LP.Policy_Id = ELP.LeavePolicy_Id'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId'
      --SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Shift S ON S.Shift_Id = PD.Shift_Id'
      SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_ShiftSettings SS ON SS.CompanyId = ' + CAST(@Id AS VARCHAR(15))
      SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_OverTimeStatus OTS ON OTS.Process_RecID = PD.Process_Rec_ID'    
      SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpLevel EL ON EL.EmployeeId = Emp.Employee_Id'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpDesignation ED ON ED.EmployeeId = Emp.Employee_Id'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpType ET ON ET.EmployeeId = Emp.Employee_Id'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpCategory EC ON EC.EmployeeId = Emp.Employee_Id'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN Ta_Tbl_levelDetails LD ON EL.LevelId = LD.Id'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN #LevelRights LR ON LR.LevelId = EL.LevelId'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Title T ON ED.DesignationId = T.TitleId'
      SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Grade G ON T.GradeId = G.Grade_ID'      
      SET @SQLQuery = @SQLQuery + ' WHERE PD.OtMins != 0 AND Emp.OT_Eligible = 1 AND Emp.CompOff_Eligible = 1'
      SET @SQLQuery = @SQLQuery + ' AND Emp.IsDeleted = 0 AND Emp.Employee_Status = 1 AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''''
      SET @SQLQuery = @SQLQuery + ' AND YS.IsActive = 1'-- AND LP.OtDayWise = 1' AND CONVERT(VARCHAR(5), DATEADD(MI, ISNULL(PD.OtMins, 0), ''00:00''), 108) != ''00:00'' 
      IF(@UserGroupName <> 'SuperAdmin')
      BEGIN
            SET @SQLQuery = @SQLQuery + ' AND G.Grade_Level >= ' + @GradeLevel  
      END
		if(@isReportingto > 0)
            BEGIN
               set @SQLQuery = @SQLQuery + ' and (Emp.Employee_ID IN (select * from #EmployeeIds)) '  
            END
      IF NOT(@EmployeeName = '')
      BEGIN
            SET @SQLQuery = @SQLQuery + ' AND (Emp.Employee_FirstName + '' '' + ISNULL(Emp.Employee_LastName, '''') LIKE ''' + @EmployeeName + '%'')'
      END
      IF(@EmployeeCategory <> 0)
      BEGIN
            SET @SQLQuery = @SQLQuery + ' AND EC.CategoryId = ' + CAST(@EmployeeCategory AS VARCHAR(10))
      END
      IF(@EmployeeType<>0)
      BEGIN
            SET @SQLQuery = @SQLQuery + ' AND ET.TypeId = ' + CAST(@EmployeeType AS VARCHAR(10))
      END
      IF NOT(@EmpId='')
      BEGIN
            SET @SQLQuery = @SQLQuery + ' AND Emp.Employee_Code_Id = ''' + CAST(@EmpId AS VARCHAR(50)) + ''''
      END
      IF NOT(@EmpSwipeCard = '')  
    BEGIN
            SET @SQLQuery = @SQLQuery + ' AND Emp.SwipeCardNo = ''' + @EmpSwipeCard + ''''
      END
      SET @SQLQuery = @SQLQuery + ' ORDER BY PD.Employee_Code_Id, PD.[Date]'
      --print(@SQLQuery)
      insert into #OTHours EXEC(@SQLQuery)
      DECLARE @SQL nvarchar(max)
      set @SQL=''
      set @SQL='select * from #OTHours '
      IF NOT(@Status = '' OR @Status = 'All')
      BEGIN
            IF(@Status = 'Pending')
            BEGIN
                  SET @SQL = @SQL + ' where (OvertimeStatus IS NULL OR OvertimeStatus = ''' + @Status + ''')'
            END
            ELSE
            BEGIN
                SET @SQL = @SQL + ' where OvertimeStatus = ''' + @status + ''''
            END
    END
      EXEC(@SQL)
      DROP TABLE #EmpLevel
      DROP TABLE #EmpDesignation
      DROP TABLE #EmpType
      DROP TABLE #EmpCategory
      DROP TABLE #LevelRights
END

--- Script 3---
ALTER PROCEDURE [dbo].[Proc_TA_GetRemainingNumberOfLeavesForSuccessiveYear]
(
	@Employee_Id int,
	@LeaveType_Id int,
	@YearSettingId int
)
AS
Begin
	SET NOCOUNT ON;
	declare @LeaveApplicationCount as int
	declare @CurrentyearSettingId int
	Declare @SuccessiveYearSettingId int
	Declare @SuccesiveLeavepolicyCount float
	Declare @MaxapplicableDays float
	
	select @CurrentyearSettingId=yearsettings_Id from ta_tbl_yearsetting where Isactive=1	
	set @SuccessiveYearSettingId = (select top 1 yearsettings_Id from ta_tbl_yearsetting where yearSettings_Id > (select Yearsettings_Id from ta_tbl_yearsetting where Isactive=1))
	
	if(@LeaveType_Id<>-1)
	begin
		if(@YearSettingId = @CurrentyearSettingId)
		begin
			set @LeaveApplicationCount=(select count(la.NumberOfDays) from Ta_Tbl_LeaveApplication la
			inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=la.YearSettings_Id 
			where ys.YearSettings_Id=@YearSettingId and la.LeaveType_Id=@LeaveType_Id 
			and la.Employee_Id=@Employee_Id and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
			and la.status not in('Rejected') and la.isdeleted=0 and la.IsBorrow=0)

			if(@LeaveApplicationCount > 0)
			begin	
				Select (ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0))- (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0)) as RemainingNoOfLeaves,ISNULL(@LeaveApplicationCount,0) as availed 
				from TA_tbl_Yearclosure yc
				inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id
				inner join Ta_Tbl_LeaveApplication la on la.LeaveType_Id =yc.LeaveTypeId and yc.employee_Id=la.Employee_id and la.YearSettings_Id=ys.YearSettings_Id
				where yc.employee_Id=@Employee_Id and la.employee_Id=@Employee_Id and yc.LeaveTypeId=@LeaveType_Id and la.LeaveType_Id=@LeaveType_Id and ys.YearSettings_Id=@YearSettingId and la.Isdeleted=0 and la.Status in ('Pending','Forwarded','Approved') and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and la.IsBorrow=0 Group by Yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment
			end
			else 
			begin
				declare @TempYearSettingsId int
				set @TempYearSettingsId= (select Yearsettings_Id from ta_tbl_yearSetting where Isactive=1)				
				select (ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0)) as RemainingNoOfLeaves,ISNULL(@LeaveApplicationCount,0) as availed from TA_tbl_Yearclosure yc
				inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id 
				where yc.employee_Id=@Employee_Id and yc.LeaveTypeId=@LeaveType_Id and ys.YearSettings_Id=@YearSettingId Group by Yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment 
			end
		end
		else if(@YearSettingId=@SuccessiveYearSettingId)
		begin	
			set @MaxapplicableDays= (select top 1 MaxDays_applicable from ta_tbl_leavepolicydetails where policy_Id=(select top 1 Leavepolicy_Id from ta_tbl_emp_leavepolicy where EmployeeId=@Employee_Id and LeaveType_Id=@LeaveType_Id order by ta_tbl_emp_leavepolicy.ToDate desc))
			select @SuccesiveLeavepolicyCount=sum(NumberofDays) from ta_tbl_LeaveApplication where YearSettings_Id=@YearSettingId and Isdeleted=0 and status <> 'Rejected' and Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and Employee_Id=@Employee_Id and LeaveType_Id=@LeaveType_Id
			if @SuccesiveLeavepolicyCount is null
			begin
				set @SuccesiveLeavepolicyCount=0.0
			end	
			select @MaxapplicableDays - @SuccesiveLeavepolicyCount as RemainingNoOfLeaves,ISNULL(@LeaveApplicationCount,0) as availed
		end
	end
	else
	begin
		if(@YearSettingId = @CurrentyearSettingId)
		begin
			Select lt.LeaveType as 'Leave Type',(ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0))- (ISNULL(sum(la.NumberOfDays),0.0) + ISNULL(yc.Encashment,0.0)) as 'Number of Days'
			from TA_tbl_Yearclosure yc
			inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id 
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=yc.LeaveTypeID
			left outer join Ta_Tbl_LeaveApplication la on la.LeaveType_Id =lt.LeaveType_Id and la.YearSettings_Id=ys.YearSettings_Id
			where yc.employee_Id=@Employee_Id and la.employee_Id=@Employee_Id and ys.YearSettings_Id=@YearSettingId and la.Isdeleted=0 
			and la.Status in ('Pending','Forwarded','Approved') and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
			and la.IsBorrow=0 Group by Yc.LeaveTypeId,yc.Carry_PrevYear,yc.Current_Quota,yc.Borrow_NextYear,yc.Encashment,lt.LeaveType_Id,lt.LeaveType
			union
			Select lt.LeaveType as 'Leave Type',(ISNULL(yc.carry_PrevYear,0.0)+ ISNULL(yc.Current_Quota,0.0)) - ISNULL(yc.Encashment,0.0) as 'Number of Days'
			from TA_tbl_Yearclosure yc
			inner join TA_Tbl_YearSetting ys on yc.Year_SettingID=ys.YearSettings_Id
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=yc.LeaveTypeID
			where yc.employee_Id=@Employee_Id and yc.LeaveTypeId not in (
			select LeaveType_Id from Ta_Tbl_LeaveApplication la
			inner join TA_Tbl_YearSetting ys on la.YearSettings_ID=ys.YearSettings_Id
			where Employee_Id=@Employee_Id and Isdeleted=0 and IsBorrow=0 
			and Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and Status not in ('Rejected') and ys.IsActive=1)
			and ys.YearSettings_Id=@YearSettingId
		end
		else if (@YearSettingId=@SuccessiveYearSettingId)
		begin	
			Select lt.LeaveType as 'Leave Type',(ISNULL(LPD.MaxDays_Applicable,0.0))
			- (ISNULL(sum(la.NumberOfDays),0.0)) as 'Number of Days'
			from ta_tbl_leavepolicydetails LPD
			inner join TA_Tbl_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id 
			inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=@YearSettingId
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=LPD.LeaveType_Id
			left outer join Ta_Tbl_LeaveApplication la on la.LeaveType_Id =lt.LeaveType_Id and la.YearSettings_Id=ys.YearSettings_Id
			where elp.EmployeeId=@Employee_Id 
			and la.employee_Id=@Employee_Id and ys.YearSettings_Id=@YearSettingId and la.Isdeleted=0 
			and la.Status in ('Pending','Forwarded','Approved') and la.Type not in ('Restricted Holiday','Comp Off','ESI','LOP') 
			and la.IsBorrow=0 
			and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
			inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid 
			where TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id and TA_Tbl_YearSetting.IsActive=1 order by TA_Tbl_Emp_LeavePolicy.ToDate desc)
			Group by lt.LeaveType_Id,LPD.MaxDays_Applicable,lt.LeaveType
			union
			Select lt.LeaveType as 'Leave Type',(ISNULL(LPD.MaxDays_Applicable,0.0)) as 'Number of Days'
			from ta_tbl_leavepolicydetails LPD 
			inner join TA_Tbl_Emp_LeavePolicy elp on elp.LeavePolicy_Id=lpd.Policy_Id 
			inner join TA_Tbl_YearSetting ys on ys.YearSettings_Id=@YearSettingId
			inner join TA_Tbl_LeaveType lt on lt.LeaveType_Id=LPD.LeaveType_Id
			where elp.EmployeeId=@Employee_Id and LPD.Leavetype_Id not in(
			select LeaveType_Id from Ta_Tbl_LeaveApplication la
			where la.Employee_Id=@Employee_Id and Isdeleted=0 and IsBorrow=0 
			and Type not in ('Restricted Holiday','Comp Off','ESI','LOP') and Status not in ('Rejected')and la.YearSettings_Id=@YearSettingId)
			and ys.YearSettings_Id=@YearSettingId
			and elp.ToDate =(select top 1 TA_Tbl_Emp_LeavePolicy.ToDate from TA_Tbl_Emp_LeavePolicy
			inner join TA_Tbl_YearSetting on TA_Tbl_YearSetting.YearSettings_Id=TA_Tbl_Emp_LeavePolicy.YearSettingid 
			where TA_Tbl_Emp_LeavePolicy.EmployeeId=@Employee_Id and TA_Tbl_YearSetting.IsActive=1 order by TA_Tbl_Emp_LeavePolicy.ToDate desc)
		end
	end
end

--- Script 4----
ALTER     PROCEDURE [dbo].[Proc_TA_SelectEmpName] 
(
	@Employee_ID int,
	@EmpCode varchar(50),
	@UserGroupName varchar(250),
	@GradeLevel varchar(20),
	@level varchar(2000),
	@ParentID int,
	@isFrom nvarchar(50)
)
AS    
BEGIN  
	set nocount on
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@Employee_ID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@Employee_ID) as EmpAutoId
	DECLARE @sql AS VARCHAR(5000) 
	DECLARE @Isdel int  
	SET @Isdel=0  
	SET @sql ='Select Employee_Id,Employee_FirstName + '' '' + Employee_LastName AS ''Employee Name'',TA_Tbl_Employee.CompOff_Eligible,Emailid ' 
	SET @sql = @sql + ' From TA_Tbl_Employee INNER JOIN TA_Tbl_EmployeeDesignation ON TA_Tbl_Employee.Employee_Id = TA_Tbl_EmployeeDesignation.EmployeeId '
	SET @sql = @sql + ' INNER JOIN TA_Tbl_EmployeeLevel ON TA_Tbl_Employee.Employee_Id = TA_Tbl_EmployeeLevel.EmployeeId '
	SET @sql = @sql + ' INNER JOIN TA_Tbl_Title ON TA_Tbl_Title.TitleId=TA_Tbl_EmployeeDesignation.DesignationId '
	SET @sql = @sql + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = TA_Tbl_Title.GradeId '
	SET @sql = @sql + ' INNER JOIN #LevelRights ON #LevelRights.LevelId = TA_Tbl_EmployeeLevel.LevelId '
	SET @sql = @sql + ' WHERE Employee_Code_Id=''' + @EmpCode + ''' '  
	SET @sql = @sql + ' AND  TA_Tbl_Employee.Employee_Code_Id<>''18000000''  AND TA_Tbl_Employee.IsDeleted=''' + cast(@Isdel as nvarchar(20)) + '''  AND TA_Tbl_Employee.Employee_Status=''1'' '
	SET @sql = @sql + ' And TA_Tbl_EmployeeLevel.EffectiveDate = (select top 1 EffectiveDate from TA_Tbl_EmployeeLevel where EmployeeId = ta_tbl_Employee.Employee_Id AND EffectiveDate <=CONVERT(DATETIME,getdate(),102) order by EffectiveDate desc)'
	if(@isReportingto > 0)
            BEGIN
               set @sql = @sql + ' and (TA_Tbl_Employee.Employee_ID IN (select * from #EmployeeIds)) '  
            END
    if(@isFrom='CompOff')
		begin
			set @sql = @sql + ' and TA_Tbl_Employee.CompOff_Eligible=1 and TA_Tbl_Employee.OT_Eligible=1 '  
		end
	 if(@isFrom='OT')
		begin
			set @sql = @sql + ' and TA_Tbl_Employee.OT_Eligible=1 '  
		end
	if(@UserGroupName<>'SuperAdmin')
		begin
			SET @sql = @sql + ' AND TA_Tbl_Grade.Grade_Level>=' + @GradeLevel + ''
		end
	exec(@sql)
	drop table #LevelRights
End

-- Script 5--
-- =============================================  
-- Author:  Karthikeyan.R
-- Create date: 08-Feb-2017
-- Description: <Description,,>  
-- Proc_TA_CompensationListing null,null,'01/01/1900','01/01/1900','Superadmin',0
 -- =============================================  
ALTER PROCEDURE [dbo].[Proc_TA_OvertimeListingByEmpId]  
@Employee_Id int,
@GradeLevel varchar(20),
@fromdate datetime = '01/01/1900', 
@ToDate datetime = '01/01/1900',
@UserGroupName varchar(250),
@ParentId int
AS  
BEGIN
	SET NOCOUNT ON;  
	Declare @sqlQuery nvarchar(Max)  
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId	
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	DECLARE @dateFormat as int
	DECLARE @TimeFormat as nvarchar(10)
	Select @dateFormat=DateFormat from Func_TA_GetDateTimeFormat()
	Select @TimeFormat=TimeFormat from Func_TA_GetDateTimeFormat()
	declare @ELTodate as datetime
	if(@ToDate='01/01/1900')
	begin
		set @ELTodate=getdate()
	end
	else
	begin
		set @ELTodate=@ToDate
	end
	DECLARE @sqlEmpLevel nVARCHAR(4000) 
	 SET @sqlEmpLevel=
      'INSERT INTO #EmpLevel SELECT Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate,ELevel.Employee_Code_Id,ELevel.SwipeCardNo 
      from  (
      SELECT      max(el.EffectiveDate) as EffectiveDate,   el.EmployeeId as EmployeeId,emp.Employee_Code_Id,emp.SwipeCardNo 
      from TA_Tbl_EmployeeLevel el
      INNER JOIN ta_tbl_employee emp ON el.EmployeeId = emp.Employee_Id 
      INNER JOIN #LevelRights ON #LevelRights.LevelId=el.LevelId '
      set @sqlEmpLevel= @sqlEmpLevel +' WHERE emp.Isdeleted=0 group by el.EmployeeId,Employee_Code_Id,emp.SwipeCardNo) ELevel
      INNER JOIN TA_Tbl_EmployeeLevel EL ON EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
      INNER JOIN #LevelRights ON #LevelRights.LevelId=EL.LevelId
      AND EL.EffectiveDate <= CONVERT(DATETIME,'''+cast(@ELTodate as nVARCHAR(50))+''', 102) and EL.LevelId is not null'      
	EXECUTE(@sqlEmpLevel)
	set @sqlQuery=''  
	set @sqlQuery='select OT.ExtraTime_Id ''Compensation_Id'',E.Employee_Code_Id as ''Employee_ID'',E.Employee_FirstName + '' ''+ isnull(E.Employee_LastName,'''') as Employee_FirstName ,
	case when '+CONVERT(varchar,@dateFormat)+' =106 then
		REPLACE(CONVERT(VARCHAR, OT.ApplicationDate,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
	else CONVERT(VARCHAR, OT.ApplicationDate, '+CONVERT(varchar,@dateFormat)+') end as ApplicationDate,
	case when '+CONVERT(varchar,@dateFormat)+' =106 then
		REPLACE(CONVERT(VARCHAR, OT.Date,'+CONVERT(varchar,@dateFormat) + '),'' '',''-'') 
	else CONVERT(VARCHAR, OT.Date, '+CONVERT(varchar,@dateFormat)+') end as FromDate,'''' Type,OT.Reason,OT.OverTimeStatus ''Status'',case	when OT.InTime<>''00:00'' then	case	when ('''+CONVERT(varchar,@TimeFormat)+ ''') =''hh:mm'' then 
	CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,OT.InTime,0)), 114)
															else	SUBSTRING(OT.InTime,13,5)+'' '' + SUBSTRING(OT.InTime,18,2) 
													end 
						else ''''
				end	InTime
	,
	case	when OT.OutTime<>''00:00'' then	case	when ('''+CONVERT(varchar,@TimeFormat)+ ''') =''hh:mm'' then 
	CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,OT.OutTime,0)), 114)
															else SUBSTRING(OT.OutTime,13,5)+'' '' + SUBSTRING(OT.OutTime,18,2)
													end 
						else ''''
				end	OutTime,OT.HrsExtra ''WorkedHours'',OT.DaysWorked ''DayWorked'',OT.OverTimeStatus ''Comp_Status'',Ta_Tbl_levelDetails.name as LevelName
	from TA_Tbl_OverTimeStatus OT inner join TA_Tbl_Employee E on OT.Employee_Id=E.Employee_Id 
	INNER JOIN TA_Tbl_EmployeeDesignation ON E.Employee_Id = TA_Tbl_EmployeeDesignation.EmployeeId 
	INNER JOIN TA_Tbl_EmployeeLevel ON E.Employee_Id = TA_Tbl_EmployeeLevel.EmployeeId
	INNER JOIN TA_Tbl_Title ON TA_Tbl_Title.TitleId=TA_Tbl_EmployeeDesignation.DesignationId 
	INNER JOIN TA_Tbl_Grade ON TA_Tbl_Grade.Grade_ID = TA_Tbl_Title.GradeId '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_levelDetails on #EmpLevel.LevelId = Ta_Tbl_levelDetails.id'
	set @sqlQuery = @sqlQuery + ' Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.LevelId where OT.isRequest=1 and OT.isDelete=0 and E.Employee_Id = ''' + cast(@Employee_Id as nvarchar(100))  + ''' AND TA_Tbl_Grade.Grade_Level >=' + cast(@GradeLevel as nvarchar(100))  
	If not(@fromdate = '01/01/1900' and @ToDate = '01/01/1900')
		begin
			set @sqlQuery= @sqlQuery + ' and OT.Date>=CONVERT(DATETIME,'''+cast(@fromdate as nvarchar(50))+''',102) '  
			set @sqlQuery= @sqlQuery + ' and OT.Date<=CONVERT(DATETIME,'''+cast(@ToDate as nvarchar(50))+''',102) '  
		end
	set @sqlQuery= @sqlQuery + '  order by OT.ExtraTime_Id desc'
	exec(@sqlQuery)
	drop table #LevelRights
	drop table #EmpLevel
END

--- Script 6--- 
ALTER PROCEDURE [dbo].[Proc_TA_insertOvertimeStatus]
	@Process_Rec_ID BIGINT ,
	@Employee_Id INT ,
	@ApplicationDate datetime ,
	@Date datetime ,
	@HrsExtra VARCHAR(5) ,
	@ExtraMins INT ,
	@Status VARCHAR(50) ,
	@TopLevelId INT,
	@DaysWorked nvarchar(25),
	@InTime nvarchar(20),
	@OutTime nvarchar(20),
	@Reason nvarchar(250),
	@ExtraTime_Id bigint,
	@Approved_By nvarchar(10)
	--@tag nvarchar(2)
As
BEGIN 
	set nocount on
	declare @ProcessID as bigint
	DECLARE @Index INT 
		set @Index= 0
		DECLARE @HalfDayOTMins BIGINT, @FullDayOTMins BIGINT, @TypeOfOT BIGINT,@OtText VARCHAR(MAX)
		DECLARE @CompOffDays FLOAT, @SumOfOTMins FLOAT,@MaxDate DATETIME
		DECLARE @ValidDates INT
		DECLARE @FromDate DATETIME, @ToDate DATETIME
		DECLARE @Employee_code_Id NVARCHAR(50)
		
	
		SET @CompOffDays = 0
		SET @SumOfOTMins = 0
		SET @OtText = ''
	--IF Update then delete the record and insert again.
	--IF(@tag='D')
	BEGIN
		IF NOT EXISTS(SELECT ExtraTime_Id FROM TA_Tbl_OverTimeStatus WHERE Employee_Id=@Employee_Id and isdelete=0 and Date=@Date)
		BEGIN
			select @ProcessID=Process_RecID from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
			update TA_Tbl_ProcessedData set OvertimeStatus=null where Process_Rec_ID=@ProcessID
			delete from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
		END
		else
		BEGIn
			IF EXISTS(SELECT ExtraTime_Id FROM TA_Tbl_OverTimeStatus WHERE ExtraTime_Id=@ExtraTime_Id)
			BEGIn
				select @ProcessID=Process_RecID from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
				update TA_Tbl_ProcessedData set OvertimeStatus=null where Process_Rec_ID=@ProcessID
				delete from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
			END
			else
			BEGIN
				SELECT '0'
			END
		END
	END
	IF NOT EXISTS(SELECT ExtraTime_Id FROM TA_Tbl_OverTimeStatus WHERE Employee_Id=@Employee_Id and isdelete=0 and Date=@Date)
	BEGIN
		--Declare the temp table for the insert and update perpose.
		DECLARE @TblRecords TABLE(Process_RecID BIGINT, Employee_Id INT,ApplicationDate varchar(20),[Date] varchar(20), HrsExtra VARCHAR(5), ExtraMins INT, OverTimeStatus VARCHAR(50), CompOffEligible BIT, OTEligible BIT, DateToExpired DATETIME, NoOfDays NUMERIC(18,1), RemainingDays NUMERIC(18,1), CompOffSatus VARCHAR(30), TopLevelId INT, UpdatedRow VARCHAR(1),DaysWorked nvarchar(20),InTime nvarchar(20),OutTime nvarchar(20),Reason nvarchar(250))
		-- Insert the values from variable
		INSERT INTO @TblRecords(Process_RecID, Employee_Id,ApplicationDate, [Date], HrsExtra, ExtraMins, OverTimeStatus, CompOffEligible, OTEligible, TopLevelId,DaysWorked, InTime, OutTime,Reason) values (@Process_Rec_ID,@Employee_Id,@ApplicationDate,@Date ,@HrsExtra ,@ExtraMins,@Status,1,0,@TopLevelId,@DaysWorked,@InTime,@OutTime,@Reason)
		SELECT @Employee_code_Id = Employee_Code_Id FROM TA_Tbl_Employee WHERE Employee_Id = @Employee_Id AND IsDeleted=0
		SELECT @FromDate = FromDate,@ToDate = ToDate FROM TA_Tbl_YearSetting WHERE YearSettings_Id in (select YearSettings_Id from TA_Tbl_YearSetting where IsActive=1)
			SELECT		@TypeOfOT = OtHourWise,
						@HalfDayOTMins = DATEDIFF(MI,'00:00', ISNULL(OtMinHrs, '00:00')),
						@FullDayOTMins = DATEDIFF(MI,'00:00',ISNULL(FullDayOTMinHrs, '00:00')),
						@ValidDates = ISNULL(LP.CompOffValidDays, 60)
						FROM TA_Tbl_LeavePolicy LP INNER JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.LeavePolicy_Id = LP.Policy_Id
						WHERE ELP.EmployeeId = @Employee_Id AND ELP.ToDate = (SELECT TOP 1 ELP.ToDate FROM TA_Tbl_Emp_LeavePolicy ELP
						INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId
						WHERE YS.IsActive = 1 AND ELP.EmployeeId = @Employee_Id ORDER BY ELP.ToDate DESC)
		update @TblRecords set DateToExpired=(DATEADD(D, @ValidDates, [Date])),NoOfDays=(CASE WHEN ExtraMins >= @FullDayOTMins THEN 1.0 WHEN ExtraMins >= @HalfDayOTMins THEN 0.5 ELSE 0.0 END),RemainingDays=(CASE WHEN ExtraMins >= @FullDayOTMins THEN 1.0 WHEN ExtraMins >= @HalfDayOTMins THEN 0.5 ELSE 0.0 END),CompOffSatus=(CASE WHEN DATEADD(D,ISNULL(@ValidDates, 0),[Date]) < GETDATE() THEN 'Expired' ELSE 'Not Taken' END)
		--Comment by karthikeyan.R 
		--IF(@TypeOfOT=1)
				--	BEGIN
				--		SELECT 
				--			@SumOfOTMins = (@SumOfOTMins + OtMins),
				--			@MaxDate = (CASE WHEN @SumOfOTMins >= @FullDayOTMins THEN [Date] WHEN @SumOfOTMins >= @HalfDayOTMins THEN [Date] ELSE NULL END),
				--			@OtText = (CASE WHEN ISNULL(@MaxDate, '') = '' THEN @OtText ELSE (@OtText + ';' + CAST(Process_Rec_ID AS VARCHAR(50)) + '&' + CAST((CASE WHEN @SumOfOTMins >= @FullDayOTMins THEN @FullDayOTMins WHEN @SumOfOTMins >= @HalfDayOTMins THEN @HalfDayOTMins ELSE @SumOfOTMins END) AS VARCHAR(10))) END),
				--			@SumOfOTMins = (CASE WHEN ISNULL(@MaxDate, '') = '' THEN @SumOfOTMins ELSE (CASE WHEN @SumOfOTMins >= @FullDayOTMins THEN 0 WHEN @SumOfOTMins >= @HalfDayOTMins THEN (@SumOfOTMins - @HalfDayOTMins) ELSE @SumOfOTMins END) END),
				--			@MaxDate = NULL
				--		FROM TA_Tbl_ProcessedData
				--			WHERE ([Date] BETWEEN @FromDate AND @ToDate) AND Employee_Code_Id = @Employee_code_Id 
				--			AND (DATEADD(D, @ValidDates, [Date])) >= @Date AND OtMins > 0 AND ISNULL(OtMins, '') != ''
				--		ORDER BY [Date]
				--	END
			--UPDATE @TblRecords SET 
		--	DateToExpired = CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved')
		--	AND TR.CompOffEligible = 1 THEN DATEADD(D,ISNULL(SS.VaildityCompOffDays, 0), CONVERT(datetime,TR.[Date],103)) END,
		--	NoOfDays =	CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved') AND TR.CompOffEligible = 1 THEN	
		--									(	CASE	WHEN TR.ExtraMins / DATEDIFF(MI,'00:00' ,SS.FullDayCompOff) > 0 THEN '1.0' 
		--													WHEN TR.ExtraMins/ DATEDIFF(MI,'00:00' ,SS.HalfDayCompOff) > 0 THEN '0.5' 
		--										END
		--									) 
		--							END,
		--	RemainingDays = CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved') AND TR.CompOffEligible = 1 THEN (CASE WHEN TR.ExtraMins / DATEDIFF(MI,'00:00' ,SS.FullDayCompOff) > 0 THEN '1.0' WHEN TR.ExtraMins/ DATEDIFF(MI,'00:00' ,SS.HalfDayCompOff) > 0 THEN '0.5' END) END,
		--	CompOffSatus = CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved') AND TR.CompOffEligible = 1 THEN (CASE WHEN DATEADD(D,ISNULL(SS.VaildityCompOffDays, 0),CONVERT(datetime,TR.[Date],103)) < GETDATE() THEN 'Expired' ELSE 'Not Taken' END) END
		--FROM @TblRecords TR
		--INNER JOIN TA_Tbl_ProcessedData PD ON PD.Process_Rec_ID = TR.Process_RecID
		--LEFT JOIN TA_Tbl_ShiftSettings SS ON SS.CompanyId = TR.TopLevelId
		--select * from @TblRecords
		INSERT INTO TA_Tbl_OverTimeStatus(Process_RecID, Employee_Id,ApplicationDate, [Date], HrsExtra, ExtraMins, OverTimeStatus, CompOffEligible, OTEligible, DateToExpired, NoOfDays, RemainingDays, CompOffSatus,isRequest,DaysWorked,InTime, OutTime,Reason,isDelete)
			SELECT Process_RecID, Employee_Id,CONVERT(datetime,ApplicationDate,103),CONVERT(datetime,[Date],103), 
			HrsExtra, ExtraMins, OverTimeStatus, CompOffEligible, OTEligible, 
			DateToExpired, NoOfDays, RemainingDays, CompOffSatus,1,DaysWorked,InTime,OutTime,Reason,0 FROM @TblRecords 
			--select * from @TblRecords
			
		SELECT '1' 
	END	 
	else 
	begin
		SELECT '0'    
	END
END
--- Script 7---
ALTER PROCEDURE [dbo].[Proc_TA_OvertimeEntryByID]
	-- Add the parameters for the stored procedure here
	@CompOff_ID as bigint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @LeaveApplication as nvarchar(50)
	SELECT @LeaveApplication=LeaveApplicationId FROM TA_Tbl_OverTimeStatus WHERE ExtraTime_Id=@CompOff_ID
	if(@LeaveApplication is null or @LeaveApplication='')
		Begin
			update TA_Tbl_ProcessedData set OvertimeStatus='Canceled' where Process_Rec_ID in (select Process_Rec_ID from TA_Tbl_OverTimeStatus where ExtraTime_Id=@CompOff_ID)
			update TA_Tbl_OverTimeStatus set isdelete=1,OverTimeStatus='Canceled' where ExtraTime_Id=@CompOff_ID
			Select '1'
		END
	else
		BEGIn
			Select 'Availed'
		END
END


-- Script 8--

ALTER PROCEDURE [dbo].[Proc_TA_SelectOvertimebyID]   
	@CompOffID bigint
AS    
BEGIN    
	SET NOCOUNT ON;    
	DECLARE @dateFormat as int
	DECLARE @TimeFormat as nvarchar(10)
	Select @dateFormat=DateFormat,@TimeFormat=TimeFormat  from Func_TA_GetDateTimeFormat()	
	begin    
		select	C.ExtraTime_Id 'Compensation_Id',
				E.Employee_Code_Id as 'Employee_Id',C.Employee_Id as E_Employee_Id,
				isnull(e.Employee_FirstName,'') +  space(1) + isnull(e.Employee_LastName,'') as Employee_FirstName,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, C.ApplicationDate,@dateFormat),' ','-') 
						else CONVERT(VARCHAR, C.ApplicationDate, @dateFormat) 
				end AS ApplicationDate,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, C.Date,@dateFormat),' ','-') 
						else CONVERT(VARCHAR, C.Date, @dateFormat) 
				end AS FromDate ,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, C.Date,@dateFormat),' ','-') 
						else CONVERT(VARCHAR, C.Date, @dateFormat) 
				end AS ToDate,
				case C.NoOfDays when '1.0' then 'FullDay' when '0.5' then 'HalfDay' 
				end as Type,
				C.Reason,
				case	when cast(@dateFormat as varchar(100))=106 then REPLACE(CONVERT(VARCHAR, C.DateToExpired,@dateFormat),' ','-') 
						else CONVERT(VARCHAR, C.DateToExpired, @dateFormat) 
				end AS ExpiryDate,
				C.NoOfDays as 'Quota',
				case	when C.DateToExpired >= CONVERT(DATETIME,cast(getdate() as nvarchar(50)),102) then
						0  else 1 
				end as Expiredstatus,
				case	when C.InTime<>'00:00' then	case	when (@TimeFormat) ='hh:mm' then CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,C.InTime,0)), 114) 
															else	InTime
													end 
						else ''
				end	InTime,
				case	when C.OutTime<>'00:00' then case	when (@TimeFormat) ='hh:mm' then  CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,C.OutTime,0)), 114)
															else	OutTime
													 end 
						else '' 
				end	OutTime,
				C.HrsExtra as 'WorkedHours',
				C.DaysWorked as 'DayWorked',
				C.OverTimeStatus as Comp_Status,
				C.Process_RecID
		from TA_Tbl_OverTimeStatus C
		inner join ta_tbl_Employee E on C.Employee_Id=E.Employee_Id 
		where	C.ExtraTime_Id=@CompOffID order by C.ExtraTime_Id desc
	end    
END
-- Script 9-- 
ALTER PROCEDURE [dbo].[Proc_TA_OvertimeListing]  
 -- Add the parameters for the stored procedure here  
 @EmployeeFirstName nvarchar(MAX),  
 @EmployeeId nvarchar(MAX),  
 @fromdate nvarchar(40),  
 @ToDate nvarchar(40),
 @UserGroupName nvarchar(250),
 @ParentID int,
 @EmpAutoID int
AS  
BEGIN
	SET NOCOUNT ON;  
	Declare @sqlQuery nvarchar(Max)  
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId	
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	DECLARE @dateFormat as int
	DECLARE @TimeFormat as nvarchar(10)
	Select @dateFormat=DateFormat from Func_TA_GetDateTimeFormat()
	Select @TimeFormat=TimeFormat from Func_TA_GetDateTimeFormat()
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@EmpAutoID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) as EmpAutoId
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate],[LevelId],[EmployeeId] ASC
	) ON [PRIMARY]
	declare @ELTodate as datetime
	if(@ToDate='01/01/1900')
	begin
		set @ELTodate=getdate()
	end
	else
	begin
		set @ELTodate=@ToDate
	end
	DECLARE @sqlEmpLevel nVARCHAR(4000) 
	 SET @sqlEmpLevel=
      'INSERT INTO #EmpLevel SELECT Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate,ELevel.Employee_Code_Id,ELevel.SwipeCardNo 
      from  (
      SELECT      max(el.EffectiveDate) as EffectiveDate,   el.EmployeeId as EmployeeId,emp.Employee_Code_Id,emp.SwipeCardNo 
      from TA_Tbl_EmployeeLevel el
      INNER JOIN ta_tbl_employee emp ON el.EmployeeId = emp.Employee_Id 
      INNER JOIN #LevelRights ON #LevelRights.LevelId=el.LevelId '
      set @sqlEmpLevel= @sqlEmpLevel +' WHERE emp.Isdeleted=0 group by el.EmployeeId,Employee_Code_Id,emp.SwipeCardNo) ELevel
      INNER JOIN TA_Tbl_EmployeeLevel EL ON EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
      INNER JOIN #LevelRights ON #LevelRights.LevelId=EL.LevelId
      AND EL.EffectiveDate <= CONVERT(DATETIME,'''+cast(@ELTodate as nVARCHAR(50))+''', 102) and EL.LevelId is not null'      
	EXECUTE(@sqlEmpLevel)
	--select * from #LevelRights
	--select * from #EmpLevel
	set @sqlQuery=''  
	set @sqlQuery='select  OT.ExtraTime_Id ''Compensation_Id'',E.Employee_Code_Id as ''Employee_ID'',E.Employee_FirstName + '' ''+ isnull(E.Employee_LastName,'''') as Employee_FirstName ,
	case when '+CONVERT(varchar,@dateFormat)+' =106 then
		REPLACE(CONVERT(VARCHAR, OT.ApplicationDate,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
	else CONVERT(VARCHAR, OT.ApplicationDate, '+CONVERT(varchar,@dateFormat)+') end as ApplicationDate,
	case when '+CONVERT(varchar,@dateFormat)+' =106 then
		REPLACE(CONVERT(VARCHAR, OT.Date,'+CONVERT(varchar,@dateFormat) + '),'' '',''-'') 
	else CONVERT(VARCHAR, OT.Date, '+CONVERT(varchar,@dateFormat)+') end as FromDate,'''' Type,OT.Reason,OT.OverTimeStatus ''Status'',
	case	when OT.InTime<>''00:00'' then	case	when ('''+CONVERT(varchar,@TimeFormat)+ ''') =''hh:mm'' then 
	CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,OT.InTime,0)), 114)
															else SUBSTRING(OT.InTime,13,5)+'' '' + SUBSTRING(OT.InTime,18,2)
													end 
						else ''''
				end	InTime
	,
	case	when OT.OutTime<>''00:00'' then	case	when ('''+CONVERT(varchar,@TimeFormat)+ ''') =''hh:mm'' then CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,OT.OutTime,0)), 114)
															else	SUBSTRING(OT.OutTime,13,5)+'' '' + SUBSTRING(OT.OutTime,18,2) 
													end 
						else ''''
				end	OutTime
,OT.HrsExtra ''WorkedHours'',OT.DaysWorked ''DayWorked'',OT.OverTimeStatus ''Comp_Status'',Ta_Tbl_levelDetails.name as LevelName
	from TA_Tbl_OverTimeStatus OT inner join TA_Tbl_Employee E on OT.Employee_Id=E.Employee_Id  '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_levelDetails on #EmpLevel.LevelId = Ta_Tbl_levelDetails.id'
	set @sqlQuery = @sqlQuery + ' Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.LevelId where OT.isRequest=1 and OT.isDelete=0 '
	if(@EmployeeFirstName<>'')  
		Begin
			set @sqlQuery = @sqlQuery + ' and (E.Employee_FirstName +'' '' + isnull(E.Employee_LastName,'''') like ''' + cast(@EmployeeFirstName+'%' as nvarchar(100)) + ''')'
		End
	if(@EmployeeId<> '')  
		Begin
			set @sqlQuery = @sqlQuery + ' and e.Employee_Code_Id = ''' + cast(@EmployeeId as nvarchar(50)) + ''''
		End
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and e.Employee_ID IN (select * from #EmployeeIds) '  
            END
	If not(@fromdate = '01/01/1900' and @ToDate = '01/01/1900')
		begin
			set @sqlQuery= @sqlQuery + ' and OT.Date>=CONVERT(DATETIME,'''+cast(@fromdate as nvarchar(50))+''',102) '  
			set @sqlQuery= @sqlQuery + ' and OT.Date<=CONVERT(DATETIME,'''+cast(@ToDate as nvarchar(50))+''',102) '  
		end
	set @sqlQuery= @sqlQuery + ' order by OT.ExtraTime_Id desc'
	--print @sqlQuery
	exec(@sqlQuery)
	drop table #LevelRights
	drop table #EmpLevel
END


-- Script 10 --
/****** Object:  StoredProcedure [dbo].[Proc_TA_RptEmployeeCompOffStatus]    Script Date: 06-12-2025 14:22:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*   
Procedure Name	:	Proc_TA_EmployeeCompOffOTSanction
Tables Included :	TA_Tbl_Employee,TA_Tbl_ProcessedData,TA_Tbl_EmployeeLevel,Ta_Tbl_levelDetails,TA_Tbl_EmployeeCategory
					TA_Tbl_Category,TA_Tbl_EmployeeType,TA_Tbl_EmployeePay,TA_Tbl_Type,TA_Tbl_EmployeeDesignation,TA_Tbl_Title
					TA_Tbl_Grade,ta_tbl_shift
Modified On		:   20-Jun-2016
Description		:	Report Issue
Modified By		:   Karthikeyan.R
Description		:	Remove The Date Convertion In Expaire Date Calculation
EXEC Proc_TA_RptEmployeeCompOffStatus @FromDate='2015-01-01 00:00:00',@ToDate='2015-10-03 00:00:00',@EmpId=N'',@ShiftId=0,@EmployeeName=N'',@LevelID=N'0',@EmployeeCategory=0,@EmployeeType=0,@UserGroupName=N'SuperAdmin',@GradeLevel=N'1',@Status=N'All',@EmpSwipeCard=N'',@ParentId=0,@DesignationID=0,@Gender=N'Both'
*/
ALTER PROCEDURE [dbo].[Proc_TA_RptEmployeeCompOffStatus]
(
	@FromDate DATETIME,
	@ToDate DATETIME,
	@EmpId VARCHAR(100),
	@ShiftId INT,
	@EmployeeName VARCHAR(50),
	@LevelID VARCHAR(8000),
	@EmployeeCategory INT, 
	@EmployeeType INT,
	@UserGroupName VARCHAR(250),
	@GradeLevel VARCHAR(20),
	@Status VARCHAR(20)	,
	@EmpSwipeCard VARCHAR(20),
	@ParentId INT,
	@DesignationID INT,
    @Gender VARCHAR(10),
    @EmpAutoID int
)
AS
BEGIN
	set nocount on;
	DECLARE @SQLQuery VARCHAR(MAX)
	SET @SQLQuery = ''
	--Get the Current date time format
	DECLARE @dateFormat as int
	DECLARE @date as nvarchar(25)			
	SET @date = (SELECT DateFormat FROM TA_Tbl_DateandTimeFormat)	
	IF(@date='dd/MM/yyyy')
		BEGIN
			SET @dateFormat = 103
		END
	ELSE IF(@date='MM/dd/yyyy')
		BEGIN
			SET @dateFormat = 101
		END
	ELSE
		BEGIN
			SET @dateFormat = 106
		END
	--End
--Temporary table's creation
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	CREATE TABLE #LevelRights(LevelId INT)
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@EmpAutoID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) as EmpAutoId
	INSERT INTO #LevelRights 
		SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID)
--Employee Level Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate] ,[LevelId],[EmployeeId] ASC
	) ON [PRIMARY]
--Employee Designation Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation] 
	(
		[EffectiveDate] ,[DesignationId],[EmployeeId] ASC
	)ON [PRIMARY]
--Employee Type Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpType] 
	(
		[EffectiveDate],[TypeId],[EmployeeId] ASC
	) ON [PRIMARY]
--Employee Category Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpCategory] 
	(
		[EffectiveDate],[CategoryId],[EmployeeId] ASC
	)ON [PRIMARY]
insert into #EmpDesignation exec Proc_TA_EmpDesignation @ToDate,@empId,@DesignationID	
	insert into #EmpType exec [Proc_TA_EmpType] @ToDate,@empId,@employeeType	
	insert into #EmpCategory exec [Proc_TA_EmpCategory] @ToDate,@empId,@employeeCategory
	--To split up the comma separated values
	if(@empId is not null and @empId <> '')
		begin
			create table #EmployeeID(EmployeeID varchar(25))
			insert into #EmployeeID
			select * from SplitupEmployeeCode(@empId)
		end
			if(@EmployeeName is not null and @EmployeeName <> '')
		begin
			create table #EmployeeName(EmployeeID varchar(25))
			insert into #EmployeeName
			select [Value] from dbo.[SplitUpEmployeeName](@EmployeeName,',')
		end
	Declare @sqlEmpLevel nvarchar(4000)	
	set @sqlEmpLevel=
'insert into #EmpLevel select Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate,Employee_Code_Id,SwipeCardNo  from (
select max(el.EffectiveDate) as EffectiveDate,el.EmployeeId as EmployeeId,emp.Employee_Code_Id,emp.SwipeCardNo from TA_Tbl_EmployeeLevel el
inner join ta_tbl_employee emp on el.EmployeeId = emp.Employee_Id 
inner join #LevelRights on #LevelRights.LevelId=el.LevelId
where emp.Isdeleted=0 group by EmployeeId,Employee_Code_Id,emp.SwipeCardNo) ELevel
inner join TA_Tbl_EmployeeLevel EL on EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
inner join #LevelRights on #LevelRights.LevelId=EL.LevelId
AND EL.EffectiveDate <= CONVERT(DATETIME,'''+cast(@ToDate as nvarchar(50))+''', 102) and EL.LevelId is not null'	
If Not (@empId = '')  
		Begin  
			SET @sqlEmpLevel = @sqlEmpLevel + ' Inner join #EmployeeID on #EmployeeID.EmployeeID=ELevel.SwipeCardNo '
		End
		If Not (@EmployeeName = '')  
		Begin  
			SET @sqlEmpLevel = @sqlEmpLevel + ' Inner join #EmployeeName on #EmployeeName.EmployeeID=ELevel.Employee_Code_Id '
		End
execute(@sqlEmpLevel)	
--AND CONVERT(VARCHAR(5), DATEADD(MI, ISNULL(PD.OtMins, 0), ''00:00''), 108) != ''00:00''
--AND CONVERT(VARCHAR(5), DATEADD(MI, ISNULL(PD.OtMins, 0), ''00:00''), 108) != ''00:00'' 
	SET @SQLQuery = 'SELECT Emp.SwipeCardNo, RTRIM(LTRIM(Emp.Employee_FirstName + SPACE(1) + ISNULL(Emp.Employee_LastName , ''''))) EmployeeName,'
	SET @SQLQuery = @SQLQuery + ' Emp.Gender,case	when ' + CONVERT(varchar,@dateFormat) +'=106 then REPLACE(CONVERT(VARCHAR(11), PD.[Date], '+ CONVERT(varchar,@dateFormat) +'),'' '',''-'') else CONVERT(VARCHAR(11), PD.[Date], '+ CONVERT(varchar,@dateFormat) +') end AS [Date], PD.HrsExtra,PD.ActHrsExtra, PD.OTSanctioned, PD.CompoffSanctioned, PD.OverTimeStatus, PD.CompOffSatus, ISNULL(S.Shift_ShortName, ''Off'') AS Shift_ShortName , PD.[NoOfDaysTaken], PD.ExtraMins,PD.ActExtraMins FROM'
	SET @SQLQuery = @SQLQuery + ' (SELECT OTS.Employee_Id, OTS.Process_RecID,  OTS.[Date] [Date], OTS.HrsExtra,isnull(OTS.ActHrsExtra,OTS.HrsExtra) [ActHrsExtra], (CASE WHEN OTS.OTEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) OTSanctioned,'
	SET @SQLQuery = @SQLQuery + ' (CASE WHEN OTS.CompOffEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) CompoffSanctioned, OTS.OverTimeStatus, (CASE WHEN OTS.OverTimeStatus = ''Approved'' AND OTS.CompOffEligible = 1 THEN ''Taken'' ELSE ''-'' END) [CompOffSatus], PD.Employee_Code_Id, PD.Shift_Id, (ISNULL(OTS.NoOfDays, 0.0) - ISNULL(OTS.RemainingDays, 0.0)) [NoOfDaysTaken], OTS.ExtraMins,ISNULL(OTS.ActExtraMins,OTS.ExtraMins) [ActExtraMins] FROM TA_Tbl_OverTimeStatus OTS'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_ProcessedData PD ON PD.Process_Rec_ID = OTS.Process_RecID'
	SET @SQLQuery = @SQLQuery + ' WHERE PD.OtMins != 0 AND OTS.NoOfDays > OTS.RemainingDays AND ISNULL(OTS.LeaveApplicationId, '''') != '''' AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''' UNION ALL' 
	SET @SQLQuery = @SQLQuery + ' SELECT OTS.Employee_Id, OTS.Process_RecID, CONVERT(VARCHAR(11), OTS.[Date], 101) [Date], OTS.HrsExtra,isnull(OTS.ActHrsExtra,OTS.HrsExtra) [ActHrsExtra], (CASE WHEN OTS.OTEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) OTSanctioned,'
	SET @SQLQuery = @SQLQuery + ' (CASE WHEN OTS.CompOffEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) CompoffSanctioned, OTS.OverTimeStatus, (CASE WHEN ISNULL(OTS.DateToExpired, PD.[Date]) < GETDATE() AND OTS.CompOffEligible = 1 THEN ''Expired'' WHEN OTS.CompOffEligible = 0 THEN ''-'' ELSE ISNULL(OTS.CompOffSatus, OTS.OverTimeStatus) END) [CompOffSatus], PD.Employee_Code_Id, PD.Shift_Id, ISNULL(OTS.RemainingDays, 0.0) [NoOfDaysTaken], OTS.ExtraMins,ISNULL(OTS.ActExtraMins,OTS.ExtraMins) [ActExtraMins] FROM TA_Tbl_OverTimeStatus OTS'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_ProcessedData PD ON PD.Process_Rec_ID = OTS.Process_RecID'
	SET @SQLQuery = @SQLQuery + ' WHERE PD.OtMins != 0 AND (OTS.RemainingDays > 0.0 OR OTS.RemainingDays IS NULL) AND ISNULL(OTS.CompOffSatus, ''Not Taken'') != ''Taken'' AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''' UNION ALL'
	SET @SQLQuery = @SQLQuery + ' SELECT EMP.Employee_Id, PD.Process_Rec_ID, CONVERT(VARCHAR(11), PD.[Date], 101) [Date],'
	SET @SQLQuery = @SQLQuery + ' (CASE WHEN PD.HrsOT != ''00:00'' THEN PD.HrsOT WHEN PD.NormOT != ''00:00'' THEN PD.NormOT WHEN PD.OffOT != ''00:00'' THEN PD.OffOT WHEN PD.HolOT != ''00:00'' THEN PD.HolOT END) [HrsExtra],'
SET @SQLQuery = @SQLQuery + ' (CASE WHEN isnull(PD.ActHrsOT,PD.HrsOT) != ''00:00'' THEN isnull(PD.ActHrsOT,PD.HrsOT) WHEN isnull(PD.ActNormOT,PD.NormOT) != ''00:00'' THEN isnull(PD.ActNormOT,PD.NormOT) WHEN isnull(PD.ActOffOT,PD.OffOT) != ''00:00'' THEN isnull(PD.ActOffOT,PD.OffOT) WHEN isnull(PD.ActHolOT,PD.HolOT) != ''00:00'' THEN isnull(PD.ActHolOT,PD.HolOT) END) [ActHrsExtra],'	
	SET @SQLQuery = @SQLQuery + ' ''00:00'' OTSanctioned, ''00:00'' CompoffSanctioned, ISNULL(PD.OverTimeStatus, ''Pending'') OverTimeStatus,'
	SET @SQLQuery = @SQLQuery + ' ISNULL(PD.OverTimeStatus, ''Pending'') [CompOffSatus],'
	SET @SQLQuery = @SQLQuery + ' PD.Employee_Code_Id,PD.Shift_Id, 0.0 [NoOfDaysTaken],PD.OtMins, isnull(PD.ActOtMins,PD.OtMins) [OtMins]  FROM TA_Tbl_ProcessedData PD'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Employee Emp ON PD.Employee_Code_Id = Emp.SwipeCardNo'	
	SET @SQLQuery = @SQLQuery + ' WHERE Emp.OT_Eligible = 1 AND Emp.CompOff_Eligible = 1 AND PD.OtMins != 0 AND PD.Process_Rec_ID NOT IN (SELECT Process_RecID FROM TA_Tbl_OverTimeStatus) AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''') PD' 
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Employee Emp ON PD.Employee_Code_Id = Emp.SwipeCardNo'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_LeavePolicy LP ON LP.Policy_Id = ELP.LeavePolicy_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Shift S ON S.Shift_Id = PD.Shift_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpLevel EL ON EL.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpDesignation ED ON ED.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpType ET ON ET.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpCategory EC ON EC.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN Ta_Tbl_levelDetails LD ON EL.LevelId = LD.Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #LevelRights LR ON LR.LevelId = EL.LevelId'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Title T ON ED.DesignationId = T.TitleId'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Grade G ON T.GradeId = G.Grade_ID'	 
	SET @SQLQuery = @SQLQuery + ' WHERE Emp.OT_Eligible = 1 AND Emp.CompOff_Eligible = 1'
	SET @SQLQuery = @SQLQuery + ' AND Emp.IsDeleted = 0 AND Emp.Employee_Status = 1'
	SET @SQLQuery = @SQLQuery + ' AND YS.IsActive = 1 AND Emp.Report_Exclude = 0 AND' --LP.OtDayWise = 1 AND 
	SET @SQLQuery = @SQLQuery + ' ((Emp.Employee_Status=1) OR (Emp.Employee_Status = 0 AND CONVERT(NVARCHAR(11), Emp.InActiveDate, 102) >= CONVERT(NVARCHAR(11), PD.[Date], 102)))'	
	IF(@UserGroupName <> 'SuperAdmin')
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND G.Grade_Level >= ' + @GradeLevel  
	END
	IF(@ShiftId<>0) 
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND PD.[Shift_Id] = ' + CAST(@ShiftId AS NVARCHAR(50))  
	END
	--IF NOT(@EmployeeName = '')
	--BEGIN
	--Declare @Tempname nvarchar(200)
 --        set @Tempname= '(Emp.Employee_FirstName + '' '' + isnull(Emp.Employee_LastName,'''')'
	--     set @sqlQuery = @sqlQuery + ' and '+ @Tempname +'= ''' + cast(@EmployeeName as nvarchar(100)) + ''')' 
	--	--SET @SQLQuery = @SQLQuery + ' AND (Emp.Employee_FirstName + '' '' + ISNULL(Emp.Employee_LastName, '''') LIKE ''' + @EmployeeName + '%'')'
	--END
	IF(@EmployeeCategory <> 0)
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND EC.CategoryId = ' + CAST(@EmployeeCategory AS VARCHAR(10))
	END
	IF(@EmployeeType<>0)
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND ET.TypeId = ' + CAST(@EmployeeType AS VARCHAR(10))
	END
	IF NOT(@EmpSwipeCard = '')  
    BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Emp.SwipeCardNo = ''' + @EmpSwipeCard + ''''
	END
	--IF NOT(@EmpId='')
	--BEGIN
	--	SET @SQLQuery = @SQLQuery + ' AND Emp.Employee_Code_Id = ''' + CAST(@EmpId AS VARCHAR(50)) + ''''
	--END
	IF(@DesignationID<>0)
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND ED.DesignationId = ' + CAST(@DesignationID AS NVARCHAR(8))
	END
	IF(@Gender<>'Both')
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Emp.Gender = ''' + CAST(@Gender AS NVARCHAR(8)) + ''''
	END
	if(@isReportingto > 0)
    BEGIN
       set @SQLQuery = @SQLQuery + ' and (Emp.Employee_ID IN (select * from #EmployeeIds)) '  
    END
	IF NOT(@Status = '' OR @Status = 'All')
	BEGIN
		IF(@Status = 'Pending')
		BEGIN
			SET @SQLQuery = @SQLQuery + ' AND (PD.OvertimeStatus IS NULL OR PD.OvertimeStatus = ''' + @Status + ''')'
		END
		ELSE
		BEGIN
		    SET @SQLQuery = @SQLQuery + ' AND PD.OvertimeStatus = ''' + @status + ''''
		END
    END
	SET @SQLQuery = @SQLQuery + ' ORDER BY PD.Employee_Code_Id, PD.[Date]'
	EXEC(@SQLQuery)
--Remove the temporary tables here
	DROP TABLE #EmpLevel
	DROP TABLE #EmpDesignation
	DROP TABLE #EmpType
	DROP TABLE #EmpCategory
	DROP TABLE #LevelRights
END


-- Script 11 ---
GO
/****** Object:  StoredProcedure [dbo].[proc_OtCalculation]    Script Date: 06-12-2025 10:45:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--select * from ta_tbl_leveldetails where parentid=0
-- proc_OtCalculation '2015/01/01','2015/12/08','',0,'','1',0,0,'SuperAdmin','','All','',0,0,'Both','OverTime'
-- Modified by : Gopinath
-- Modified Date : 11-Jan-2014
-- Description    :     SQL Query Optimazation & Emp name filtering
-- proc_OtCalculation @fromDat='2019-05-01 00:00:00',@ToDat='2019-05-06 00:00:00',@empId=N'',@shift=0,@EmployeeName=N'',@LevelID=N'1',@employeeCategory=0,@employeeType=0,@UserGroupName=N'Superadmin',@GradeLevel=N'1',@Status=N'All',@EmpSwipeCard=N'',@ParentId=0,@DesignationID=0,@Gender=N'Both',@SanctionType=N'Overtime',@EmpAutoId=1

-- exec proc_OtCalculation @fromDat='2025-11-01 00:00:00',@ToDat='2025-11-30 00:00:00',@empId=N'',@shift=0,@EmployeeName=N'',@LevelID=N'0,1',@employeeCategory=0,@employeeType=0,@UserGroupName=N'SuperAdmin',@GradeLevel=N'0',@Status=N'All',@EmpSwipeCard=N'',@ParentId=0,@DesignationID=0,@Gender=N'Both',@SanctionType=N'OverTime',@EmpAutoId=0

ALTER PROCEDURE [dbo].[proc_OtCalculation]
(
	@fromDat datetime,
	@ToDat datetime,
	@empId varchar(100),
	@shift int,
	@EmployeeName varchar(50),
	@LevelID varchar(8000),
	@employeeCategory int, 
	@employeeType int,
	@UserGroupName varchar(250),
	@GradeLevel varchar(20),
	@Status varchar(20)     ,
	@EmpSwipeCard varchar(20),
	@ParentId int,
	@DesignationID int,
	@Gender varchar(10),
	@SanctionType varchar(25),
	@EmpAutoId bigint
)
AS
	BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
	insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@EmpAutoID) as EmpAutoId  
	insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) as EmpAutoId
	--Get Current date time format
	DECLARE @dateFormat as int
	DECLARE @date as nvarchar(25)             
	SET @date = (SELECT DateFormat FROM TA_Tbl_DateandTimeFormat)     
	IF(@date='dd/MM/yyyy')
		BEGIN
			SET @dateFormat = 103
		END
	ELSE IF(@date='MM/dd/yyyy')
		BEGIN
			SET @dateFormat = 101
		END
	ELSE
		BEGIN
			SET @dateFormat = 106
		END   
	--End
	--Temporary table's creation
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId 
	--select * from #LevelRights
	CREATE TABLE #TempProcessedData
	(
		[Process_Rec_ID] bigint,
		[Employee_Code_Id] nvarchar(20),
		[Date] datetime,
		[Shift_Id] int,
		[InTime] nvarchar(25),
		[OutTime] nvarchar(25),
		[HrsWork] nvarchar(10),
		[HrsOT] nvarchar(10),
		[NormOT] nvarchar(10),
		[OffOT] nvarchar(10),
		[HolOT] nvarchar(10),
		[EarlyOut] nvarchar(10),
		[LateIn] nvarchar(10),
		[Attendance] nvarchar(20),
		[WorkHrs] nvarchar(10),
		[HrsLess] nvarchar(10),
		[HrsExtra] nvarchar(10),
		[LateInMins] float,
		[EarlyOutMins] float,
		[ExtraMins] float,
		[OtMins] float,
		[OvertimeStatus] nvarchar(50),
		[TotalDays] numeric(18, 1),
		[LeaveCode] nvarchar(20),
		[LateEntryReason] nvarchar(260),
		[EarlyOutReason] nvarchar(260),
		[ProcessFlag] bit,
		[Status] nvarchar(50),
		[FNAttendanceCode] nvarchar(15),
		[ANAttendanceCode] nvarchar(15),
		[ActHrsOT] nvarchar(10),
		[ActNormOT] nvarchar(10),
		[ActOffOT] nvarchar(10),
		[ActHolOT] nvarchar(10),
		[ActOtMins] float,
		[AvailOtMins] float,
		[ExtraTime_Id] bigint,
		
		-----
				SPEC_NormOT1	varchar(10),
				SPEC_NormOTMins1	int,
				SPEC_NormOT2	varchar(10),
				SPEC_NormOTMins2	int,
				SPEC_OffOT1		varchar(10),
				SPEC_OffOTMins1	int,
				SPEC_OffOT2		varchar(10),
				SPEC_OffOTMins2	int,
				SPEC_HOLOT1		varchar(10),
				SPEC_HOLOTMins1	int,
				SPEC_HOLOT2		varchar(10),
				SPEC_HOLOTMins2	int,			
				
				Modified_Spec_NormOTMins1 int,
				Modified_Spec_NormOTMins2 int,
				Modified_Spec_OffOTMins1 int,
				Modified_Spec_OffOTMins2 int,
				Modified_Spec_HolOTMins1 int,
				Modified_Spec_HolOTMins2 int,
				Modified_Spec_NormOTHrs1 varchar(10),
				Modified_Spec_NormOTHrs2 varchar(10),
				Modified_Spec_OffOTHrs1 varchar(10),
				Modified_Spec_OffOTHrs2 varchar(10),
				Modified_Spec_HolOTHrs1 varchar(10),
				Modified_Spec_HolOTHrs2 varchar(10),
				Reason nvarchar(50)
				
	)
	--Employee Level Indexes
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel]([EffectiveDate],[LevelId],[EmployeeId] ASC) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation]([EffectiveDate],[DesignationId],[EmployeeId] ASC)ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpType]([EffectiveDate],[TypeId],[EmployeeId] ASC) ON [PRIMARY]
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpCategory]([EffectiveDate],[CategoryId],[EmployeeId] ASC)ON [PRIMARY]
	insert into #EmpDesignation exec Proc_TA_EmpDesignation @ToDat,@empId,@DesignationID  
	insert into #EmpType exec [Proc_TA_EmpType] @ToDat,@empId,@employeeType 
	insert into #EmpCategory exec [Proc_TA_EmpCategory] @ToDat,@empId,@employeeCategory     
	insert into #EmpLevel exec  [Proc_TA_EmpLevel] @ToDat,@empId      
	Declare @sqlTempProcessedData1 varchar(8000)
	--To split up the comma separated values
	if(@empId is not null and @empId <> '')
		begin
			create table #EmployeeID(EmployeeID varchar(25))
			insert into #EmployeeID select * from SplitupEmployeeCode(@empId)
		end
	IF(@EmployeeName IS NOT NULL AND @EmployeeName <> '')
		BEGIN
			CREATE TABLE #EMPLOYEENAME(EMPLOYEEID VARCHAR(25))
			INSERT INTO #EMPLOYEENAME
			SELECT [VALUE] FROM DBO.[SPLITUPEMPLOYEENAME](@EmployeeName,',')
		END   
	create table #TempEmployee(EmpAutoId bigint,EmpSwipeCardNo varchar(20))
	declare @GetLevelID as int
	declare @TopLevelId as int
	set @GetLevelID=(select top 1 LevelId from #EmpLevel)
	DECLARE @childID INT 
	;WITH ParentLevel AS
	(
		SELECT *, 1 AS Lvl FROM Ta_Tbl_levelDetails WHERE id = @GetLevelID
		UNION ALL
		SELECT rh.*, Lvl+1 AS Lvl FROM Ta_Tbl_levelDetails rh
		INNER JOIN ParentLevel rc ON rh.id = rc.ParentId
	)
	SELECT TOP 1 @TopLevelId=id
	FROM ParentLevel r
	ORDER BY lvl DESC
	declare @isRequestCompOff nvarchar(10)
	select @isRequestCompOff=isRequestingforCompOff from TA_Tbl_ShiftSettings where CompanyId=@TopLevelId
	if(@isRequestCompOff is null or @isRequestCompOff='')
		BEGIN
			select @isRequestCompOff=isRequestingforCompOff from TA_Tbl_ShiftSettings where CompanyId=-1
		END
	Declare @sqlTempProcessedData varchar(8000)    
	set @sqlTempProcessedData=
	'insert into #TempEmployee select Employee_ID,SwipeCardNo from TA_Tbl_Employee
	inner join #EmpLevel on #EmpLevel.EmployeeId=TA_Tbl_Employee.Employee_Id 
	inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.LevelId '
	If Not (@empId = '')  
		Begin  
			SET @sqlTempProcessedData = @sqlTempProcessedData + ' Inner join #EmployeeID on #EmployeeID.EmployeeID=TA_Tbl_Employee.Employee_code_Id '
		End
	IF NOT (@EMPLOYEENAME = '')  
		BEGIN  
			SET @sqlTempProcessedData = @sqlTempProcessedData + ' Inner join #EmployeeName on #EmployeeName.EmployeeID=TA_Tbl_Employee.Employee_code_Id '
		END
	SET @sqlTempProcessedData = @sqlTempProcessedData + ' where SwipeCardNo is not null and TA_Tbl_Employee.Isdeleted=0'     
	Execute(@sqlTempProcessedData)
	Declare @sqlCommonEmployee_Code_Id nvarchar(1000)
	
	
	if(@isRequestCompOff=0 or @SanctionType = 'OverTime')
		BEGIN
			update TA_Tbl_ProcessedData set 
			OvertimeStatus=OT.OvertimeStatus from TA_Tbl_OverTimeStatus OT inner join TA_Tbl_Employee E on E.Employee_Id=OT.Employee_Id inner join TA_Tbl_ProcessedData P on OT.Date=P.Date and E.Employee_Code_Id=P.Employee_Code_Id where OT.isDelete=0 and OT.isRequest=1
			set @sqlTempProcessedData=
			'insert into #TempProcessedData
			select PD.Process_Rec_ID,PD.Employee_Code_Id,PD.Date,PD.Shift_Id,PD.InTime,PD.OutTime,PD.HrsWork,PD.HrsOT,PD.NormOT,PD.OffOT,PD.HolOT,PD.EarlyOut,
			PD.LateIn,PD.Attendance,PD.WorkHrs,PD.HrsLess,PD.HrsExtra,PD.LateInMins,PD.EarlyOutMins,PD.ExtraMins,PD.OtMins,PD.OvertimeStatus,PD.TotalDays,
			PD.LeaveCode,PD.LateEntryReason,PD.EarlyOutReason,PD.ProcessFlag,PD.Status,PD.FNAttendanceCode,PD.ANAttendanceCode,PD.ActHrsOT,PD.ActNormOT,PD.ActOffOT,PD.ActHolOT,PD.ActOtMins,case when ISNULL(ActOTMins,0)=0 then [OtMins] else ActOTMins end,0,
			PD.SPEC_NormOT1,
				PD.SPEC_NormOTMins1,
				PD.SPEC_NormOT2,
				PD.SPEC_NormOTMins2,
				PD.SPEC_OffOT1,
				PD.SPEC_OffOTMins1,
				PD.SPEC_OffOT2,
				PD.SPEC_OffOTMins2,
				PD.SPEC_HOLOT1,
				PD.SPEC_HOLOTMins1,
				PD.SPEC_HOLOT2,
				PD.SPEC_HOLOTMins2,
				spot.Modified_Spec_NormOTMins1,
				spot.Modified_Spec_NormOTMins2,
				spot.Modified_Spec_OffOTMins1,
				spot.Modified_Spec_OffOTMins2,
				spot.Modified_Spec_HolOTMins1,
				spot.Modified_Spec_HolOTMins2,
				spot.Modified_Spec_NormOTHrs1,
				spot.Modified_Spec_NormOTHrs2,
				spot.Modified_Spec_OffOTHrs1,
				spot.Modified_Spec_OffOTHrs2,
				spot.Modified_Spec_HolOTHrs1,
				spot.Modified_Spec_HolOTHrs2,
				OT.Reason
			from TA_Tbl_ProcessedData as PD inner join #TempEmployee on #TempEmployee.EmpSwipeCardNo=PD.Employee_Code_Id 
			Left outer join Ta_tbl_specialOvertimeDetails spot on (PD.Employee_Code_Id=spot.Employee_code_id and PD.Date=spot.Date)
			Left Join TA_Tbl_OverTimeStatus OT on OT.Process_RecID=PD.Process_Rec_ID
			where (PD.Date >= CONVERT(DATETIME,'''+cast( @fromDat as nvarchar(50))+''', 102)) 
			AND (PD.Date <= CONVERT(DATETIME,'''+cast(@todat as nvarchar(50))+''', 102)) and (PD.OtMins <> 0 or ActOTMins <> 0)'
			Execute(@sqlTempProcessedData)
		END
	else
		BEGIN
			set @sqlTempProcessedData=' insert into #TempProcessedData 
			select OT.Process_RecID as ''Process_Rec_ID'',E.Employee_Code_Id,OT.Date,p.Shift_Id,P.InTime,P.OutTime,P.HrsWork,P.HrsOT,P.NormOT,P.OffOT,P.HolOT,p.EarlyOut,p.LateIn,
			p.Attendance,p.WorkHrs,p.HrsLess,p.HrsExtra,p.LateInMins,p.EarlyOutMins,p.ExtraMins,p.OtMins,OT.OverTimeStatus,p.TotalDays,p.LeaveCode,p.LateEntryReason,p.EarlyOutReason,
			p.ProcessFlag,p.Status,p.FNAttendanceCode,p.ANAttendanceCode,p.ActHrsOT,p.ActNormOT,p.ActOffOT,p.ActHolOT,p.ActOtMins,OT.ExtraMins,OT.ExtraTime_Id,
			P.SPEC_NormOT1,
			P.SPEC_NormOTMins1,
			P.SPEC_NormOT2,
			P.SPEC_NormOTMins2,
			P.SPEC_OffOT1,
			P.SPEC_OffOTMins1,
			P.SPEC_OffOT2,
			P.SPEC_OffOTMins2,
			P.SPEC_HOLOT1,
			P.SPEC_HOLOTMins1,
			P.SPEC_HOLOT2,
			P.SPEC_HOLOTMins2,
			spot.Modified_Spec_NormOTMins1,
			spot.Modified_Spec_NormOTMins2,
			spot.Modified_Spec_OffOTMins1,
			spot.Modified_Spec_OffOTMins2,
			spot.Modified_Spec_HolOTMins1,
			spot.Modified_Spec_HolOTMins2,
			spot.Modified_Spec_NormOTHrs1,
			spot.Modified_Spec_NormOTHrs2,
			spot.Modified_Spec_OffOTHrs1,
			spot.Modified_Spec_OffOTHrs2,
			spot.Modified_Spec_HolOTHrs1,
			spot.Modified_Spec_HolOTHrs2,
			OT.Reason
			from TA_Tbl_OverTimeStatus OT inner join TA_Tbl_Employee E on E.Employee_Id=OT.Employee_Id 
			inner join TA_Tbl_ProcessedData P on OT.Date=P.Date and E.Employee_Code_Id=P.Employee_Code_Id  
			Left outer join Ta_tbl_specialOvertimeDetails spot on P.Employee_Code_Id=spot.Employee_code_id	
			where E.Employee_Code_Id in (
			select SwipeCardNo from TA_Tbl_Employee inner join
			#EmpLevel on #EmpLevel.EmployeeId=E.Employee_Id)
			and P.[Date] between '''+cast( @fromDat as nvarchar(50))+''' and '''+cast(@todat as nvarchar(50))+'''  '
			Execute(@sqlTempProcessedData)
		END
	--select * from #TempProcessedData where OtMins <>0
	declare @sqlQuery as varchar(8000)
	set @sqlQuery = ''	
	set @sqlQuery = @sqlQuery + 'select E.Employee_Code_Id,
										E.Employee_FirstName + space(1) + isnull(E.employee_lastname,'''') as Employee_FirstName,
										replace(Ta_Tbl_levelDetails.name,''&amp;'',''&'')  AS [Level Name],
										replace(Ta_tbl_Title.TitleName,''&amp;'',''&'') as TitleName,
										E.Gender,
										case	when '+CONVERT(varchar,@dateFormat) +'=106 then REPLACE(CONVERT(VARCHAR, TPD.Date,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
												else CONVERT(VARCHAR, TPD.Date, '+CONVERT(varchar,@dateFormat)+') 
										end  as date,
										TPD.HrsWork,
										ISNULL(TPD.ActNormOT,TPD.NormOt) ActHrsOT,
										TPD.NormOt as HrsOT,
										SPEC_NormOT1,
										Modified_Spec_NormOTHrs1=ISNULL(Modified_Spec_NormOTHrs1,SPEC_NormOT1),
										SPEC_NormOT2,
										Modified_Spec_NormOTHrs2=ISNULL(Modified_Spec_NormOTHrs2,SPEC_NormOT2),
										ISNULL(TPD.ActOffOT,TPD.OffOT) ActOffOT,
										TPD.OffOT,
										SPEC_OffOT1,
										Modified_Spec_OffOTHrs1=ISNULL(Modified_Spec_OffOTHrs1,SPEC_OffOT1),
										SPEC_OffOT2,
										Modified_Spec_OffOTHrs2=ISNULL(Modified_Spec_OffOTHrs2,SPEC_OffOT2),
										ISNULL(TPD.ActHolOT,TPD.HolOT) ActHolOT,
										TPD.HolOT,									
										SPEC_HOLOT1,
										Modified_Spec_HolOTHrs1=ISNULL(Modified_Spec_HolOTHrs1,SPEC_HOLOT1),
										SPEC_HOLOT2,	
										Modified_Spec_HolOTHrs2=ISNULL(Modified_Spec_HolOTHrs2,SPEC_HOLOT2),
										ISNULL(TPD.OverTimeStatus,''Pending'') as OverTimeStatus,
										SPEC_NormOTMins1,
										SPEC_NormOTMins2,
										SPEC_OffOTMins1,
										SPEC_OffOTMins2,
										SPEC_HOLOTMins1,
										SPEC_HOLOTMins2,
										Modified_Spec_NormOTMins1,
										Modified_Spec_NormOTMins2,
										Modified_Spec_OffOTMins1,
										Modified_Spec_OffOTMins2,
										Modified_Spec_HolOTMins1,
										Modified_Spec_HolOTMins2,
										TPD.shift_id,
										ISNULL(ta_tbl_shift.Shift_ShortName,''Off'') as ShiftName, TPD.OtMins,
										CASE WHEN ISNULL(NormOt,''00:00'')=''00:00'' THEN 0 ELSE round(OtMins,0) END as ''NormOTMins'',
										CASE WHEN ISNULL(OffOT,''00:00'')=''00:00''	 THEN 0 ELSE round(OtMins,0) END as ''WeekOffOTMins'',
										CASE WHEN ISNULL(HolOT,''00:00'')=''00:00''  THEN 0 ELSE round(OtMins,0) END as ''HolidayOTMins'', 
										CASE WHEN ISNULL(ActNormOT,NormOT)=''00:00'' THEN 0 ELSE ISNULL(round(ActOtMins,0),round(OtMins,0)) END as ''ActNormOTMins'',
										CASE WHEN ISNULL(ActOffOT,OffOT)=''00:00''	 THEN 0 ELSE ISNULL(round(ActOtMins,0),round(OtMins,0)) END as ''ActWeekOffOTMins'',
										CASE WHEN ISNULL(ActHolOT,HolOT)=''00:00''	 THEN 0 ELSE ISNULL(round(ActOtMins,0),round(OtMins,0)) END as ''ActHolidayOTMins'',
										ISNULL(ActOtMins,OtMins) as ApprovedOTHours,
										(ISNULL(round(ActOtMins,0),round(OtMins,0)) - round(OtMins,0)) as RejectOTHours,										
										0 as SelectedRecords,
										E.SwipeCardNo,			
																	
										ISNULL(TPD.ActOtMins,TPD.OtMins) ActOtMins, Reason
	from #TempProcessedData TPD INNER JOIN Ta_Tbl_Employee E on TPD.Employee_Code_Id=E.SwipeCardNo'
	set @sqlQuery = @sqlQuery + ' LEFT OUTER JOIN ta_tbl_shift ON TPD.shift_id = dbo.ta_tbl_shift.shift_id '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_levelDetails on #EmpLevel.LevelId = Ta_Tbl_levelDetails.id'
	set @sqlQuery = @sqlQuery + ' Inner join #EmpDesignation on #EmpDesignation.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Title ON #EmpDesignation.DesignationId = TA_Tbl_Title.TitleId'
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Grade ON TA_Tbl_Title.GradeId = TA_Tbl_Grade.Grade_ID'  
	set @sqlQuery = @sqlQuery + ' Inner join #EmpType on  #EmpType.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpCategory on #EmpCategory.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' where (TPD.OtMins <> 0 or TPD.ActOtMins <> 0) '
	if(@SanctionType = 'CompOff')
		begin
			set @sqlQuery = @sqlQuery + 'AND E.CompOff_Eligible = 1 AND E.OT_Eligible = 0 '
		end
	ELSE if(@SanctionType = 'OverTime')
		begin
			set @sqlQuery = @sqlQuery + 'AND E.CompOff_Eligible = 0 AND E.OT_Eligible = 1 '
		end
	if(@UserGroupName<>'SuperAdmin')
		BEGIN
			SET @sqlQuery = @sqlQuery + ' AND TA_Tbl_Grade.Grade_Level>=' + @GradeLevel  
		END
	if(@isReportingto > 0)
		BEGIN
			set @sqlQuery = @sqlQuery + ' and (E.Employee_ID IN (select * from #EmployeeIds)) '  
		END
	if(@shift<>0) 
		begin
			set @sqlQuery = @sqlQuery + ' And TPD.Shift_Id=' + cast (@shift as nvarchar(50))  
		end 
	set @sqlQuery = @sqlQuery + ' And E.isdeleted=0 and E.report_exclude=0 and
	((E.employee_status=1)
	or 
	(E.employee_status=0 and convert(nvarchar(11),E.InActiveDate,102)>= convert(nvarchar(11),TPD.Date,102))
	)'
	if(@Gender<>'Both')
		begin
			set @sqlQuery = @sqlQuery + ' and E.Gender = ''' + cast(@Gender as nvarchar(8)) + ''' '
		end   
	if not(@status='' or @status='All')
		begin
			IF(@status = 'Pending')
				BEGIN
					set @sqlQuery = @sqlQuery + ' AND (TPD.OvertimeStatus IS NULL OR TPD.OvertimeStatus = ''' + @status + ''')'
				END
			ELSE
				BEGIN
					set @sqlQuery = @sqlQuery + ' AND TPD.OvertimeStatus = ''' + @status + ''''
				END
		end 
	set @sqlQuery = @sqlQuery + ' order by Employee_Code_Id,Date'
	print(@sqlQuery)	
	exec (@sqlQuery)	
	--Remove the temporary tables here 
	drop table #EmpLevel
	drop table #EmpDesignation
	drop table #EmpType
	drop table #EmpCategory
	drop table #LevelRights
	drop table #TempProcessedData
	END


-- Script 11 --
ALTER PROCEDURE [dbo].[Proc_TA_RptEmployeeCompOffStatus]
(
	@FromDate DATETIME,
	@ToDate DATETIME,
	@EmpId VARCHAR(100),
	@ShiftId INT,
	@EmployeeName VARCHAR(50),
	@LevelID VARCHAR(8000),
	@EmployeeCategory INT, 
	@EmployeeType INT,
	@UserGroupName VARCHAR(250),
	@GradeLevel VARCHAR(20),
	@Status VARCHAR(20)	,
	@EmpSwipeCard VARCHAR(20),
	@ParentId INT,
	@DesignationID INT,
    @Gender VARCHAR(10),
    @EmpAutoID int
)
AS
BEGIN
	set nocount on;
	DECLARE @SQLQuery VARCHAR(MAX)
	SET @SQLQuery = ''
	--Get the Current date time format
	DECLARE @dateFormat as int
	DECLARE @date as nvarchar(25)			
	SET @date = (SELECT DateFormat FROM TA_Tbl_DateandTimeFormat)	
	IF(@date='dd/MM/yyyy')
		BEGIN
			SET @dateFormat = 103
		END
	ELSE IF(@date='MM/dd/yyyy')
		BEGIN
			SET @dateFormat = 101
		END
	ELSE
		BEGIN
			SET @dateFormat = 106
		END
	--End
--Temporary table's creation
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	CREATE TABLE #LevelRights(LevelId INT)
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@EmpAutoID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) as EmpAutoId
	INSERT INTO #LevelRights 
		SELECT * FROM [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID)
--Employee Level Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate] ,[LevelId],[EmployeeId] ASC
	) ON [PRIMARY]
--Employee Designation Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpDesignation] 
	(
		[EffectiveDate] ,[DesignationId],[EmployeeId] ASC
	)ON [PRIMARY]
--Employee Type Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpType] 
	(
		[EffectiveDate],[TypeId],[EmployeeId] ASC
	) ON [PRIMARY]
--Employee Category Indexes
/****** Object:  Index [IDX_Effective]    Script Date: 05/27/2009 16:28:22 ******/
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpCategory] 
	(
		[EffectiveDate],[CategoryId],[EmployeeId] ASC
	)ON [PRIMARY]
insert into #EmpDesignation exec Proc_TA_EmpDesignation @ToDate,@empId,@DesignationID	
	insert into #EmpType exec [Proc_TA_EmpType] @ToDate,@empId,@employeeType	
	insert into #EmpCategory exec [Proc_TA_EmpCategory] @ToDate,@empId,@employeeCategory
	--To split up the comma separated values
	if(@empId is not null and @empId <> '')
		begin
			create table #EmployeeID(EmployeeID varchar(25))
			insert into #EmployeeID
			select * from SplitupEmployeeCode(@empId)
		end
			if(@EmployeeName is not null and @EmployeeName <> '')
		begin
			create table #EmployeeName(EmployeeID varchar(25))
			insert into #EmployeeName
			select [Value] from dbo.[SplitUpEmployeeName](@EmployeeName,',')
		end
	Declare @sqlEmpLevel nvarchar(4000)	
	set @sqlEmpLevel=
'insert into #EmpLevel select Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate,Employee_Code_Id,SwipeCardNo  from (
select max(el.EffectiveDate) as EffectiveDate,el.EmployeeId as EmployeeId,emp.Employee_Code_Id,emp.SwipeCardNo from TA_Tbl_EmployeeLevel el
inner join ta_tbl_employee emp on el.EmployeeId = emp.Employee_Id 
inner join #LevelRights on #LevelRights.LevelId=el.LevelId
where emp.Isdeleted=0 group by EmployeeId,Employee_Code_Id,emp.SwipeCardNo) ELevel
inner join TA_Tbl_EmployeeLevel EL on EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
inner join #LevelRights on #LevelRights.LevelId=EL.LevelId
AND EL.EffectiveDate <= CONVERT(DATETIME,'''+cast(@ToDate as nvarchar(50))+''', 102) and EL.LevelId is not null'	
If Not (@empId = '')  
		Begin  
			SET @sqlEmpLevel = @sqlEmpLevel + ' Inner join #EmployeeID on #EmployeeID.EmployeeID=ELevel.SwipeCardNo '
		End
		If Not (@EmployeeName = '')  
		Begin  
			SET @sqlEmpLevel = @sqlEmpLevel + ' Inner join #EmployeeName on #EmployeeName.EmployeeID=ELevel.Employee_Code_Id '
		End
execute(@sqlEmpLevel)	
--AND CONVERT(VARCHAR(5), DATEADD(MI, ISNULL(PD.OtMins, 0), ''00:00''), 108) != ''00:00''
--AND CONVERT(VARCHAR(5), DATEADD(MI, ISNULL(PD.OtMins, 0), ''00:00''), 108) != ''00:00'' 
	SET @SQLQuery = 'SELECT Emp.SwipeCardNo, RTRIM(LTRIM(Emp.Employee_FirstName + SPACE(1) + ISNULL(Emp.Employee_LastName , ''''))) EmployeeName,'
	SET @SQLQuery = @SQLQuery + ' Emp.Gender,case	when ' + CONVERT(varchar,@dateFormat) +'=106 then REPLACE(CONVERT(VARCHAR(11), PD.[Date], '+ CONVERT(varchar,@dateFormat) +'),'' '',''-'') else CONVERT(VARCHAR(11), PD.[Date], '+ CONVERT(varchar,@dateFormat) +') end AS [Date], PD.HrsExtra,PD.ActHrsExtra, PD.OTSanctioned, PD.CompoffSanctioned, PD.OverTimeStatus, PD.CompOffSatus, ISNULL(S.Shift_ShortName, ''Off'') AS Shift_ShortName , PD.[NoOfDaysTaken], PD.ExtraMins,PD.ActExtraMins FROM'
	SET @SQLQuery = @SQLQuery + ' (SELECT OTS.Employee_Id, OTS.Process_RecID,  OTS.[Date] [Date], OTS.HrsExtra,isnull(OTS.ActHrsExtra,OTS.HrsExtra) [ActHrsExtra], (CASE WHEN OTS.OTEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) OTSanctioned,'
	SET @SQLQuery = @SQLQuery + ' (CASE WHEN OTS.CompOffEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) CompoffSanctioned, OTS.OverTimeStatus, (CASE WHEN OTS.OverTimeStatus = ''Approved'' AND OTS.CompOffEligible = 1 THEN ''Taken'' ELSE ''-'' END) [CompOffSatus], PD.Employee_Code_Id, PD.Shift_Id, (ISNULL(OTS.NoOfDays, 0.0) - ISNULL(OTS.RemainingDays, 0.0)) [NoOfDaysTaken], OTS.ExtraMins,ISNULL(OTS.ActExtraMins,OTS.ExtraMins) [ActExtraMins] FROM TA_Tbl_OverTimeStatus OTS'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_ProcessedData PD ON PD.Process_Rec_ID = OTS.Process_RecID'
	SET @SQLQuery = @SQLQuery + ' WHERE PD.OtMins != 0 AND OTS.NoOfDays > OTS.RemainingDays AND ISNULL(OTS.LeaveApplicationId, '''') != '''' AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''' UNION ALL' 
	SET @SQLQuery = @SQLQuery + ' SELECT OTS.Employee_Id, OTS.Process_RecID, CONVERT(VARCHAR(11), OTS.[Date], 101) [Date], OTS.HrsExtra,isnull(OTS.ActHrsExtra,OTS.HrsExtra) [ActHrsExtra], (CASE WHEN OTS.OTEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) OTSanctioned,'
	SET @SQLQuery = @SQLQuery + ' (CASE WHEN OTS.CompOffEligible = 1 THEN OTS.HrsExtra ELSE ''00:00'' END) CompoffSanctioned, OTS.OverTimeStatus, (CASE WHEN ISNULL(OTS.DateToExpired, PD.[Date]) < GETDATE() AND OTS.CompOffEligible = 1 THEN ''Expired'' WHEN OTS.CompOffEligible = 0 THEN ''-'' ELSE ISNULL(OTS.CompOffSatus, OTS.OverTimeStatus) END) [CompOffSatus], PD.Employee_Code_Id, PD.Shift_Id, ISNULL(OTS.RemainingDays, 0.0) [NoOfDaysTaken], OTS.ExtraMins,ISNULL(OTS.ActExtraMins,OTS.ExtraMins) [ActExtraMins] FROM TA_Tbl_OverTimeStatus OTS'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_ProcessedData PD ON PD.Process_Rec_ID = OTS.Process_RecID'
	SET @SQLQuery = @SQLQuery + ' WHERE PD.OtMins != 0 AND (OTS.RemainingDays > 0.0 OR OTS.RemainingDays IS NULL) AND ISNULL(OTS.CompOffSatus, ''Not Taken'') != ''Taken'' AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''' UNION ALL'
	SET @SQLQuery = @SQLQuery + ' SELECT EMP.Employee_Id, PD.Process_Rec_ID, CONVERT(VARCHAR(11), PD.[Date], 101) [Date],'
	SET @SQLQuery = @SQLQuery + ' (CASE WHEN PD.HrsOT != ''00:00'' THEN PD.HrsOT WHEN PD.NormOT != ''00:00'' THEN PD.NormOT WHEN PD.OffOT != ''00:00'' THEN PD.OffOT WHEN PD.HolOT != ''00:00'' THEN PD.HolOT END) [HrsExtra],'
SET @SQLQuery = @SQLQuery + ' (CASE WHEN isnull(PD.ActHrsOT,PD.HrsOT) != ''00:00'' THEN isnull(PD.ActHrsOT,PD.HrsOT) WHEN isnull(PD.ActNormOT,PD.NormOT) != ''00:00'' THEN isnull(PD.ActNormOT,PD.NormOT) WHEN isnull(PD.ActOffOT,PD.OffOT) != ''00:00'' THEN isnull(PD.ActOffOT,PD.OffOT) WHEN isnull(PD.ActHolOT,PD.HolOT) != ''00:00'' THEN isnull(PD.ActHolOT,PD.HolOT) END) [ActHrsExtra],'	
	SET @SQLQuery = @SQLQuery + ' ''00:00'' OTSanctioned, ''00:00'' CompoffSanctioned, ISNULL(PD.OverTimeStatus, ''Pending'') OverTimeStatus,'
	SET @SQLQuery = @SQLQuery + ' ISNULL(PD.OverTimeStatus, ''Pending'') [CompOffSatus],'
	SET @SQLQuery = @SQLQuery + ' PD.Employee_Code_Id,PD.Shift_Id, 0.0 [NoOfDaysTaken],PD.OtMins, isnull(PD.ActOtMins,PD.OtMins) [OtMins]  FROM TA_Tbl_ProcessedData PD'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Employee Emp ON PD.Employee_Code_Id = Emp.SwipeCardNo'	
	SET @SQLQuery = @SQLQuery + ' WHERE Emp.OT_Eligible = 1 AND Emp.CompOff_Eligible = 1 AND PD.OtMins != 0 AND PD.Process_Rec_ID NOT IN (SELECT Process_RecID FROM TA_Tbl_OverTimeStatus) AND PD.[Date] BETWEEN ''' + CONVERT(VARCHAR(11), @FromDate, 101) + ''' AND ''' + CONVERT(VARCHAR(11), @ToDate, 101) + ''') PD' 
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Employee Emp ON PD.Employee_Code_Id = Emp.SwipeCardNo'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_LeavePolicy LP ON LP.Policy_Id = ELP.LeavePolicy_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId'
	SET @SQLQuery = @SQLQuery + ' LEFT JOIN TA_Tbl_Shift S ON S.Shift_Id = PD.Shift_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpLevel EL ON EL.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpDesignation ED ON ED.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpType ET ON ET.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #EmpCategory EC ON EC.EmployeeId = Emp.Employee_Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN Ta_Tbl_levelDetails LD ON EL.LevelId = LD.Id'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN #LevelRights LR ON LR.LevelId = EL.LevelId'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Title T ON ED.DesignationId = T.TitleId'
	SET @SQLQuery = @SQLQuery + ' INNER JOIN TA_Tbl_Grade G ON T.GradeId = G.Grade_ID'	 
	SET @SQLQuery = @SQLQuery + ' WHERE Emp.OT_Eligible = 1 AND Emp.CompOff_Eligible = 1'
	SET @SQLQuery = @SQLQuery + ' AND Emp.IsDeleted = 0 AND Emp.Employee_Status = 1'
	SET @SQLQuery = @SQLQuery + ' AND YS.IsActive = 1 AND Emp.Report_Exclude = 0 AND' --LP.OtDayWise = 1 AND 
	SET @SQLQuery = @SQLQuery + ' ((Emp.Employee_Status=1) OR (Emp.Employee_Status = 0 AND CONVERT(NVARCHAR(11), Emp.InActiveDate, 102) >= CONVERT(NVARCHAR(11), PD.[Date], 102)))'	
	IF(@UserGroupName <> 'SuperAdmin')
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND G.Grade_Level >= ' + @GradeLevel  
	END
	IF(@ShiftId<>0) 
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND PD.[Shift_Id] = ' + CAST(@ShiftId AS NVARCHAR(50))  
	END
	--IF NOT(@EmployeeName = '')
	--BEGIN
	--Declare @Tempname nvarchar(200)
 --        set @Tempname= '(Emp.Employee_FirstName + '' '' + isnull(Emp.Employee_LastName,'''')'
	--     set @sqlQuery = @sqlQuery + ' and '+ @Tempname +'= ''' + cast(@EmployeeName as nvarchar(100)) + ''')' 
	--	--SET @SQLQuery = @SQLQuery + ' AND (Emp.Employee_FirstName + '' '' + ISNULL(Emp.Employee_LastName, '''') LIKE ''' + @EmployeeName + '%'')'
	--END
	IF(@EmployeeCategory <> 0)
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND EC.CategoryId = ' + CAST(@EmployeeCategory AS VARCHAR(10))
	END
	IF(@EmployeeType<>0)
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND ET.TypeId = ' + CAST(@EmployeeType AS VARCHAR(10))
	END
	IF NOT(@EmpSwipeCard = '')  
    BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Emp.SwipeCardNo = ''' + @EmpSwipeCard + ''''
	END
	--IF NOT(@EmpId='')
	--BEGIN
	--	SET @SQLQuery = @SQLQuery + ' AND Emp.Employee_Code_Id = ''' + CAST(@EmpId AS VARCHAR(50)) + ''''
	--END
	IF(@DesignationID<>0)
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND ED.DesignationId = ' + CAST(@DesignationID AS NVARCHAR(8))
	END
	IF(@Gender<>'Both')
	BEGIN
		SET @SQLQuery = @SQLQuery + ' AND Emp.Gender = ''' + CAST(@Gender AS NVARCHAR(8)) + ''''
	END
	if(@isReportingto > 0)
    BEGIN
       set @SQLQuery = @SQLQuery + ' and (Emp.Employee_ID IN (select * from #EmployeeIds)) '  
    END
	IF NOT(@Status = '' OR @Status = 'All')
	BEGIN
		IF(@Status = 'Pending')
		BEGIN
			SET @SQLQuery = @SQLQuery + ' AND (PD.OvertimeStatus IS NULL OR PD.OvertimeStatus = ''' + @Status + ''')'
		END
		ELSE
		BEGIN
		    SET @SQLQuery = @SQLQuery + ' AND PD.OvertimeStatus = ''' + @status + ''''
		END
    END
	SET @SQLQuery = @SQLQuery + ' ORDER BY PD.Employee_Code_Id, PD.[Date]'
	EXEC(@SQLQuery)
--Remove the temporary tables here
	DROP TABLE #EmpLevel
	DROP TABLE #EmpDesignation
	DROP TABLE #EmpType
	DROP TABLE #EmpCategory
	DROP TABLE #LevelRights
END
--- Script 12--
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_EmployeeOTSanction]    Script Date: 05-12-2025 15:10:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*  
Procedure Name : Proc_TA_EmployeeOTSanction  
Description : To get the OT Employee with Approval Status
Tables Included : TA_Tbl_Employee,TA_Tbl_ProcessedData,TA_Tbl_EmployeeLevel,Ta_Tbl_levelDetails,TA_Tbl_EmployeeCategory
				  TA_Tbl_Category,TA_Tbl_EmployeeType,TA_Tbl_EmployeePay,TA_Tbl_Type,TA_Tbl_EmployeeDesignation,TA_Tbl_Title
				  TA_Tbl_Grade,ta_tbl_shift
Created By : Sasireka  
Created On : 08-Jan-2009 
Modified By :  Karthikeyan.R
Modified On :  14-Mar-2017

exec Proc_TA_EmployeeOTSanction @Employee_ID=1,@fromDat='2025-10-10 00:00:00',@ToDat='2025-10-10 00:00:00',@empId='',@EmployeeName='',@LevelID='1',@employeeCategory=0,@employeeType=0,@UserGroupName='SuperAdmin',@GradeLevel='0',@Status='All',@EmpSwipeCard='',@ParentId=1,@SanctionType='OverTime'

*/  
-- [Proc_TA_EmployeeOTSanction] 1,'2019/05/01','2019/05/01','','','0','','','SuperAdmin','1','All','',0,'compoff'
ALTER PROCEDURE [dbo].[Proc_TA_EmployeeOTSanction]
(
	@Employee_ID int,
	@fromDat datetime,
	@ToDat datetime,
	@empId varchar(100),
	@EmployeeName varchar(50),
	@LevelID varchar(8000),
	@employeeCategory int, 
	@employeeType int,
	@UserGroupName varchar(250),
	@GradeLevel varchar(20),
	@Status varchar(50),
    @EmpSwipeCard varchar(20),
    @ParentId int,
    @SanctionType varchar(25)   
)
AS
BEGIN
	SET NOCOUNT ON;
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime)
	create table #EmpDesignation(EmployeeId int,DesignationId int,EffectiveDate datetime)
	create table #EmpType(EmployeeId int,TypeId int,EffectiveDate datetime)
	create table #EmpCategory(EmployeeId int,CategoryId int,EffectiveDate datetime)
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@Employee_ID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@Employee_ID) as EmpAutoId
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId
	
	insert into #EmpLevel select Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate from (
	select max(el.EffectiveDate) as EffectiveDate,EmployeeId from TA_Tbl_EmployeeLevel el
	inner join ta_tbl_employee emp on el.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) ELevel
	inner join TA_Tbl_EmployeeLevel EL on EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
	AND EL.EffectiveDate <= CONVERT(DATETIME,cast(@ToDat as nvarchar(50)), 102) and EL.LevelId is not null
	--select * from #EmpLevel
	--Employee Designation insert in Temporary table
	insert into #EmpDesignation select EDesig.EmployeeId,ed.DesignationId,EDesig.EffectiveDate from (
	select max(ed.EffectiveDate) as EffectiveDate,ed.EmployeeId as EmployeeId from TA_Tbl_EmployeeDesignation ed
	inner join ta_tbl_employee emp on ed.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) EDesig
	inner join TA_Tbl_EmployeeDesignation ED on ED.EffectiveDate=EDesig.EffectiveDate and ED.employeeid=EDesig.EmployeeId
	AND ED.EffectiveDate <= CONVERT(DATETIME,cast(@ToDat as nvarchar(50)), 102) and ED.DesignationId is not null
	--select * from #EmpDesignation
	--Employee Type insert in Temporary table
	insert into #EmpType select EmpType.EmployeeId,EType.TypeId,EmpType.EffectiveDate from (
	select max(et.EffectiveDate) as EffectiveDate,et.EmployeeId as EmployeeId from TA_Tbl_EmployeeType et
	inner join ta_tbl_employee emp on et.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) EmpType
	inner join TA_Tbl_EmployeeType EType on EType.EffectiveDate=EmpType.EffectiveDate and EType.employeeid=EmpType.EmployeeId
	AND EType.EffectiveDate <=CONVERT(DATETIME,cast(@ToDat as nvarchar(50)), 102)  and EType.TypeId is not null
	--select * from #EmpType
	--Employee Category insert in Temporary table
	insert into #EmpCategory select EmpCategory.EmployeeId,ECategory.CategoryId,EmpCategory.EffectiveDate from (
	select max(ec.EffectiveDate) as EffectiveDate,ec.EmployeeId as EmployeeId from TA_Tbl_EmployeeCategory ec
	inner join ta_tbl_employee emp on ec.EmployeeId = emp.Employee_Id 
	where emp.Isdeleted=0 group by EmployeeId) EmpCategory
	inner join TA_Tbl_EmployeeCategory ECategory on ECategory.EffectiveDate=EmpCategory.EffectiveDate and ECategory.employeeid=EmpCategory.EmployeeId
	AND ECategory.EffectiveDate <=CONVERT(DATETIME,cast(@ToDat as nvarchar(50)), 102)   and ECategory.CategoryId is not null
	declare @GetLevelID as int
	declare @TopLevelId as int
	set @GetLevelID=(select top 1 LevelId from #EmpLevel)
	DECLARE @childID INT 
		;WITH ParentLevel AS
		(
			SELECT *, 1 AS Lvl FROM Ta_Tbl_levelDetails WHERE id = @GetLevelID
			UNION ALL
			SELECT rh.*, Lvl+1 AS Lvl FROM Ta_Tbl_levelDetails rh
			INNER JOIN ParentLevel rc ON rh.id = rc.ParentId
		)
	SELECT TOP 1 @TopLevelId=id
	FROM ParentLevel r
	--inner JOIN Ta_Tbl_levelDetails p ON p.id = r.ParentId
	ORDER BY lvl DESC
	--select @TopLevelId
	--update TA_Tbl_ShiftSettings set isRequestingforCompOff=1
	declare @isRequestCompOff nvarchar(10)
	select @isRequestCompOff=isRequestingforCompOff from TA_Tbl_ShiftSettings where CompanyId=@TopLevelId
	if(@isRequestCompOff is null or @isRequestCompOff='')
	BEGIN
		select @isRequestCompOff=isRequestingforCompOff from TA_Tbl_ShiftSettings where CompanyId=-1
	END
	CREATE TABLE #TempProcessedData
			(
				[Process_Rec_ID] bigint,
				[Employee_Code_Id] nvarchar(20),
				[Date] datetime,
				[Shift_Id] int,
				[InTime] nvarchar(25),
				[OutTime] nvarchar(25),
				[HrsWork] nvarchar(10),
				[HrsOT] nvarchar(10),
				[NormOT] nvarchar(10),
				[OffOT] nvarchar(10),
				[HolOT] nvarchar(10),
				[EarlyOut] nvarchar(10),
				[LateIn] nvarchar(10),
				[Attendance] nvarchar(20),
				[WorkHrs] nvarchar(10),
				[HrsLess] nvarchar(10),
				[HrsExtra] nvarchar(10),
				[LateInMins] float,
				[EarlyOutMins] float,
				[ExtraMins] float,
				[OtMins] float,
				[OvertimeStatus] nvarchar(50),
				[TotalDays] numeric(18, 1),
				[LeaveCode] nvarchar(20),
				[LateEntryReason] nvarchar(260),
				[EarlyOutReason] nvarchar(260),
				[ProcessFlag] bit,
				[Status] nvarchar(50),
				[FNAttendanceCode] nvarchar(15),
				[ANAttendanceCode] nvarchar(15),
				[ActHrsOT] nvarchar(10),
				[ActNormOT] nvarchar(10),
				[ActOffOT] nvarchar(10),
				[ActHolOT] nvarchar(10),
				[ActOtMins] float,
				[AvailOtMins] float,
				[ExtraTime_Id] bigint,
				
				-----
				SPEC_NormOT1	varchar(10),
				SPEC_NormOTMins1	int,
				SPEC_NormOT2	varchar(10),
				SPEC_NormOTMins2	int,
				SPEC_OffOT1		varchar(10),
				SPEC_OffOTMins1	int,
				SPEC_OffOT2		varchar(10),
				SPEC_OffOTMins2	int,
				SPEC_HOLOT1		varchar(10),
				SPEC_HOLOTMins1	int,
				SPEC_HOLOT2		varchar(10),
				SPEC_HOLOTMins2	int,			
				
				Modified_Spec_NormOTMins1 int,
				Modified_Spec_NormOTMins2 int,
				Modified_Spec_OffOTMins1 int,
				Modified_Spec_OffOTMins2 int,
				Modified_Spec_HolOTMins1 int,
				Modified_Spec_HolOTMins2 int,
				Modified_Spec_NormOTHrs1 varchar(10),
				Modified_Spec_NormOTHrs2 varchar(10),
				Modified_Spec_OffOTHrs1 varchar(10),
				Modified_Spec_OffOTHrs2 varchar(10),
				Modified_Spec_HolOTHrs1 varchar(10),
				Modified_Spec_HolOTHrs2 varchar(10),
				OT int,CompOff int,isRequest int,
				reason nvarchar(50)
		  )
		if(@isRequestCompOff=0 or @SanctionType = 'OverTime')
			BEGIN
				update TA_Tbl_ProcessedData set 
				OvertimeStatus=OT.OvertimeStatus from TA_Tbl_OverTimeStatus OT inner join TA_Tbl_Employee E on E.Employee_Id=OT.Employee_Id inner join TA_Tbl_ProcessedData P on OT.Date=P.Date and E.Employee_Code_Id=P.Employee_Code_Id where OT.isDelete=0 and OT.isRequest=1
			  
				insert into #TempProcessedData 
				select [Process_Rec_ID],PD.[Employee_Code_Id],pd.[Date],[Shift_Id],PD.[InTime],PD.[OutTime],[HrsWork],[HrsOT],[NormOT],[OffOT],[HolOT],
				[EarlyOut],[LateIn],[Attendance],[WorkHrs],[HrsLess],PD.[HrsExtra],[LateInMins],[EarlyOutMins],PD.[ExtraMins],[OtMins],
				PD.[OvertimeStatus],[TotalDays],[LeaveCode],[LateEntryReason],[EarlyOutReason],[ProcessFlag],[Status],[FNAttendanceCode],[ANAttendanceCode],[ActHrsOT],[ActNormOT],[ActOffOT],[ActHolOT],[ActOtMins],case when ISNULL(ActOTMins,0)=0 then [OtMins] else ActOTMins end,0,
				PD.SPEC_NormOT1,
				PD.SPEC_NormOTMins1,
				PD.SPEC_NormOT2,
				PD.SPEC_NormOTMins2,
				PD.SPEC_OffOT1,
				PD.SPEC_OffOTMins1,
				PD.SPEC_OffOT2,
				PD.SPEC_OffOTMins2,
				PD.SPEC_HOLOT1,
				PD.SPEC_HOLOTMins1,
				PD.SPEC_HOLOT2,
				PD.SPEC_HOLOTMins2,
				spot.Modified_Spec_NormOTMins1,
				spot.Modified_Spec_NormOTMins2,
				spot.Modified_Spec_OffOTMins1,
				spot.Modified_Spec_OffOTMins2,
				spot.Modified_Spec_HolOTMins1,
				spot.Modified_Spec_HolOTMins2,
				spot.Modified_Spec_NormOTHrs1,
				spot.Modified_Spec_NormOTHrs2,
				spot.Modified_Spec_OffOTHrs1,
				spot.Modified_Spec_OffOTHrs2,
				spot.Modified_Spec_HolOTHrs1,
				spot.Modified_Spec_HolOTHrs2,OT.OTEligible,OT.CompOffEligible,OT.isRequest,OT.Reason
				from TA_Tbl_ProcessedData PD
				inner join TA_Tbl_Employee e on PD.Employee_Code_Id=e.SwipeCardNo
				inner join #EmpLevel on #EmpLevel.EmployeeId=e.Employee_Id
				Left outer join Ta_tbl_specialOvertimeDetails spot on (PD.Employee_Code_Id=spot.Employee_code_id and PD.Date=spot.Date)
				Left Join TA_Tbl_OverTimeStatus OT on OT.Process_RecID=PD.Process_Rec_ID
				where (OtMins <> 0 or ActOTMins <> 0)  and 
				pd.[Date] between @fromDat and @ToDat 
			END
		Else
			BEGIN
				insert into #TempProcessedData 
				select OT.Process_RecID as 'Process_Rec_ID',E.Employee_Code_Id,OT.Date,p.Shift_Id,
				P.InTime,P.OutTime,OT.HrsExtra,OT.HrsExtra,OT.HrsExtra,OT.HrsExtra,OT.HrsExtra,
				p.EarlyOut,p.LateIn,p.Attendance,p.WorkHrs,p.HrsLess,p.HrsExtra,p.LateInMins,p.EarlyOutMins,
				p.ExtraMins,p.OtMins,OT.OverTimeStatus,p.TotalDays,p.LeaveCode,p.LateEntryReason,p.EarlyOutReason,
				p.ProcessFlag,p.Status,p.FNAttendanceCode,p.ANAttendanceCode,p.ActHrsOT,p.ActNormOT,p.ActOffOT,p.ActHolOT,
				p.ActOtMins,OT.ExtraMins,OT.ExtraTime_Id,
				P.SPEC_NormOT1,
				P.SPEC_NormOTMins1,
				P.SPEC_NormOT2,
				P.SPEC_NormOTMins2,
				P.SPEC_OffOT1,
				P.SPEC_OffOTMins1,
				P.SPEC_OffOT2,
				P.SPEC_OffOTMins2,
				P.SPEC_HOLOT1,
				P.SPEC_HOLOTMins1,
				P.SPEC_HOLOT2,
				P.SPEC_HOLOTMins2,
				spot.Modified_Spec_NormOTMins1,
				spot.Modified_Spec_NormOTMins2,
				spot.Modified_Spec_OffOTMins1,
				spot.Modified_Spec_OffOTMins2,
				spot.Modified_Spec_HolOTMins1,
				spot.Modified_Spec_HolOTMins2,
				spot.Modified_Spec_NormOTHrs1,
				spot.Modified_Spec_NormOTHrs2,
				spot.Modified_Spec_OffOTHrs1,
				spot.Modified_Spec_OffOTHrs2,
				spot.Modified_Spec_HolOTHrs1,
				spot.Modified_Spec_HolOTHrs2,OT.OTEligible,OT.CompOffEligible,OT.isRequest,OT.Reason				
				from TA_Tbl_OverTimeStatus OT 
				inner join TA_Tbl_Employee E on E.Employee_Id=OT.Employee_Id 
				inner join TA_Tbl_ProcessedData P on OT.Date=P.Date and E.Employee_Code_Id=P.Employee_Code_Id  
				inner join #EmpLevel on #EmpLevel.EmployeeId=e.Employee_Id
				Left outer join Ta_tbl_specialOvertimeDetails spot on P.Employee_Code_Id=spot.Employee_code_id	
				where P.[Date] between @fromDat and @ToDat and OT.isDelete=0 and OT.isRequest=1
			END	
	declare @sqlQuery as varchar(8000)
	set @sqlQuery = ''	
	set @sqlQuery = @sqlQuery + 'select 
	E.Employee_Code_Id,
	E.Employee_FirstName + space(1) + isnull(E.employee_lastname,'''') as Employee_FirstName,
	replace(Ta_Tbl_levelDetails.name,''&amp;'',''&'')  AS [Level Name],
	convert(varchar(20),TPD.Date,101) as date,
	TPD.NormOt as HrsOT,
	ISNULL(TPD.ActNormOT,TPD.NormOt) ActHrsOT,
	TPD.OffOT,
	ISNULL(TPD.ActOffOT,TPD.OffOT) ActOffOT,
	TPD.HolOT,
	ISNULL(TPD.ActHolOT,TPD.HolOT) ActHolOT,
	SPEC_NormOT1,
	Modified_Spec_NormOTHrs1=ISNULL(Modified_Spec_NormOTHrs1,SPEC_NormOT1),
	SPEC_NormOT2,
	Modified_Spec_NormOTHrs2=ISNULL(Modified_Spec_NormOTHrs2,SPEC_NormOT2),
	SPEC_OffOT1,
	Modified_Spec_OffOTHrs1=ISNULL(Modified_Spec_OffOTHrs1,SPEC_OffOT1),
	SPEC_OffOT2,
	Modified_Spec_OffOTHrs2=ISNULL(Modified_Spec_OffOTHrs2,SPEC_OffOT2),
	SPEC_HOLOT1,
	Modified_Spec_HolOTHrs1=ISNULL(Modified_Spec_HolOTHrs1,SPEC_HOLOT1),
	SPEC_HOLOT2,	
	Modified_Spec_HolOTHrs2=ISNULL(Modified_Spec_HolOTHrs2,SPEC_HOLOT2),
	ISNULL(TPD.OverTimeStatus,''Pending'') as OverTimeStatus, 	
	E.Employee_Id,TPD.OTMins,
	''0'' as SelectedRecords,
	ISNULL(TPD.ActOtMins,TPD.OtMins) ActOtMins,'+@isRequestCompOff +' as isRequestCompOff,TPD.ExtraTime_Id,	
	TPD.Process_Rec_ID,
	TPD.shift_id,
	TPD.HrsWork,TPD.OT,TPD.CompOff ,TPD.isRequest,TPD.Reason
	from #TempProcessedData TPD 
	INNER JOIN Ta_Tbl_Employee E on TPD.Employee_Code_Id=E.SwipeCardNo'
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpDesignation on #EmpDesignation.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpType on #EmpType.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpCategory on #EmpCategory.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_levelDetails on #EmpLevel.LevelId = Ta_Tbl_levelDetails.id'
	set @sqlQuery = @sqlQuery + ' Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.LevelId '
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Title ON #EmpDesignation.DesignationId = dbo.TA_Tbl_Title.TitleId'
	set @sqlQuery = @sqlQuery + ' INNER JOIN TA_Tbl_Grade ON dbo.TA_Tbl_Title.GradeId = dbo.TA_Tbl_Grade.Grade_ID'	 
	set @sqlQuery = @sqlQuery + ' LEFT OUTER JOIN ta_tbl_shift ON TPD.shift_id = dbo.ta_tbl_shift.shift_id '  
	set @sqlQuery = @sqlQuery + ' where TPD.AvailOtMins <> 0 '
	if(@SanctionType = 'OverTime')
		begin
			set @sqlQuery = @sqlQuery + ' AND TPD.OT = 1 AND TPD.isRequest = 1 '
		end
	ELSE IF(@SanctionType = 'compoff')
		BEGIN
			set @sqlQuery = @sqlQuery + ' AND E.CompOff_Eligible = 1 AND E.OT_Eligible = 0 '
		END	
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and (E.Employee_ID IN (select * from #EmployeeIds)) '  
            END
	if(@UserGroupName<>'SuperAdmin')
	begin
		SET @sqlQuery = @sqlQuery + ' AND TA_Tbl_Grade.Grade_Level>=' + @GradeLevel  
	end
	set @sqlQuery = @sqlQuery + ' And E.isdeleted=0 and E.report_exclude=0 and E.employee_status=1'
	if not(@EmployeeName='')
	begin
			set @sqlQuery = @sqlQuery + ' and (E.Employee_FirstName + '' '' + isnull(E.Employee_LastName,'''') like ''' + cast(@EmployeeName+'%' as nvarchar(100)) + ''')'
	end
	if(@employeeCategory<>0)
	begin
		set @sqlQuery = @sqlQuery +' and #EmpCategory.CategoryId = ' + cast(@employeeCategory as nvarchar(8))
	end
	if(@employeeType<>0)
	begin
		set @sqlQuery = @sqlQuery + ' and #EmpType.TypeId = ' + cast(@employeeType as nvarchar(8))
	end
	if not (@empId='')
	begin
			set @sqlQuery = @sqlQuery + ' and E.Employee_Code_Id = ''' + cast (@empId as nvarchar(50)) + ''' '
	end
	If Not (@EmpSwipeCard = '')
    Begin  
		  Set @sqlQuery = @sqlQuery + ' And E.SwipeCardNo = ''' + @EmpSwipeCard + ''' '  
	End
	if not(@status='' or @status='All')
	begin
	 	IF(@status = 'Pending')
			BEGIN
				set @sqlQuery = @sqlQuery + ' AND (TPD.OvertimeStatus IS NULL OR TPD.OvertimeStatus = ''' + @status + ''')'
			END
		ELSE
			BEGIN
		        set @sqlQuery = @sqlQuery + ' AND TPD.OvertimeStatus = ''' + @status + ''''
			END
	end 
	set @sqlQuery = @sqlQuery + ' order by Employee_Code_Id , Date'
	exec (@sqlQuery)
	drop table #TempProcessedData
	drop table #EmpLevel
	drop table #EmpDesignation
	drop table #EmpType
	drop table #EmpCategory
	drop table #LevelRights
END

-- Script 13--
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_insertCompOffOTStatus]    Script Date: 05-12-2025 14:54:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*  
Procedure Name	:	Proc_TA_EmployeeOTStatus  
Description		:	To Update the OverTime Status in ta_tbl_processedData  table
Tables Included :	ta_tbl_ProcessedData,ta_tbl_Employee  
Created By		:	Karthikeyan.R
Created On		:	21-Mar-2017
Proc_TA_insertCompOffOTStatus 23685,45,'2017/04/11','2017/04/02','12:00',720,'Approved',1,'Weekly Off','09:00','21:00','Test',0,'I'
*/  
ALTER PROCEDURE [dbo].[Proc_TA_insertCompOffOTStatus]
	@Process_Rec_ID BIGINT ,
	@Employee_Id INT ,
	@ApplicationDate datetime ,
	@Date datetime ,
	@HrsExtra VARCHAR(5) ,
	@ExtraMins INT ,
	@Status VARCHAR(50) ,
	@TopLevelId INT,
	@DaysWorked nvarchar(25),
	@InTime nvarchar(20),
	@OutTime nvarchar(20),
	@Reason nvarchar(250),
	@ExtraTime_Id bigint,
	@tag nvarchar(2)
As
BEGIN 
	set nocount on
	declare @ProcessID as bigint
	DECLARE @Index INT 
		set @Index= 0
		DECLARE @HalfDayOTMins BIGINT, @FullDayOTMins BIGINT, @TypeOfOT BIGINT,@OtText VARCHAR(MAX)
		DECLARE @CompOffDays FLOAT, @SumOfOTMins FLOAT,@MaxDate DATETIME
		DECLARE @ValidDates INT
		DECLARE @FromDate DATETIME, @ToDate DATETIME
		DECLARE @Employee_code_Id NVARCHAR(50)
		SET @CompOffDays = 0
		SET @SumOfOTMins = 0
		SET @OtText = ''
	--IF Update then delete the record and insert again.
	IF(@tag='D')
	BEGIN
		IF NOT EXISTS(SELECT ExtraTime_Id FROM TA_Tbl_OverTimeStatus WHERE Employee_Id=@Employee_Id and isdelete=0 and Date=@Date)
		BEGIN
			select @ProcessID=Process_RecID from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
			update TA_Tbl_ProcessedData set OvertimeStatus=null where Process_Rec_ID=@ProcessID
			delete from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
		END
		else
		BEGIn
			IF EXISTS(SELECT ExtraTime_Id FROM TA_Tbl_OverTimeStatus WHERE ExtraTime_Id=@ExtraTime_Id)
			BEGIn
				select @ProcessID=Process_RecID from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
				update TA_Tbl_ProcessedData set OvertimeStatus=null where Process_Rec_ID=@ProcessID
				delete from TA_Tbl_OverTimeStatus where ExtraTime_Id=@ExtraTime_Id
			END
			else
			BEGIN
				SELECT '0'
			END
		END
	END
	IF NOT EXISTS(SELECT ExtraTime_Id FROM TA_Tbl_OverTimeStatus WHERE Employee_Id=@Employee_Id and isdelete=0 and Date=@Date)
	BEGIN
		--Declare the temp table for the insert and update perpose.
		DECLARE @TblRecords TABLE(Process_RecID BIGINT, Employee_Id INT,ApplicationDate varchar(20),[Date] varchar(20), HrsExtra VARCHAR(5), ExtraMins INT, OverTimeStatus VARCHAR(50), CompOffEligible BIT, OTEligible BIT, DateToExpired DATETIME, NoOfDays NUMERIC(18,1), RemainingDays NUMERIC(18,1), CompOffSatus VARCHAR(30), TopLevelId INT, UpdatedRow VARCHAR(1),DaysWorked nvarchar(20),InTime nvarchar(20),OutTime nvarchar(20),Reason nvarchar(250))
		-- Insert the values from variable
		INSERT INTO @TblRecords(Process_RecID, Employee_Id,ApplicationDate, [Date], HrsExtra, ExtraMins, OverTimeStatus, CompOffEligible, OTEligible, TopLevelId,DaysWorked, InTime, OutTime,Reason) values (@Process_Rec_ID,@Employee_Id,@ApplicationDate,@Date ,@HrsExtra ,@ExtraMins,@Status,1,0,@TopLevelId,@DaysWorked,@InTime,@OutTime,@Reason)
		SELECT @Employee_code_Id = Employee_Code_Id FROM TA_Tbl_Employee WHERE Employee_Id = @Employee_Id AND IsDeleted=0
		SELECT @FromDate = FromDate,@ToDate = ToDate FROM TA_Tbl_YearSetting WHERE YearSettings_Id in (select YearSettings_Id from TA_Tbl_YearSetting where IsActive=1)
			SELECT		@TypeOfOT = OtHourWise,
						@HalfDayOTMins = DATEDIFF(MI,'00:00', ISNULL(OtMinHrs, '00:00')),
						@FullDayOTMins = DATEDIFF(MI,'00:00',ISNULL(FullDayOTMinHrs, '00:00')),
						@ValidDates = ISNULL(LP.CompOffValidDays, 60)
						FROM TA_Tbl_LeavePolicy LP INNER JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.LeavePolicy_Id = LP.Policy_Id
						WHERE ELP.EmployeeId = @Employee_Id AND ELP.ToDate = (SELECT TOP 1 ELP.ToDate FROM TA_Tbl_Emp_LeavePolicy ELP
						INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId
						WHERE YS.IsActive = 1 AND ELP.EmployeeId = @Employee_Id ORDER BY ELP.ToDate DESC)
		update @TblRecords set DateToExpired=(DATEADD(D, @ValidDates, [Date])),NoOfDays=(CASE WHEN ExtraMins >= @FullDayOTMins THEN 1.0 WHEN ExtraMins >= @HalfDayOTMins THEN 0.5 ELSE 0.0 END),RemainingDays=(CASE WHEN ExtraMins >= @FullDayOTMins THEN 1.0 WHEN ExtraMins >= @HalfDayOTMins THEN 0.5 ELSE 0.0 END),CompOffSatus=(CASE WHEN DATEADD(D,ISNULL(@ValidDates, 0),[Date]) < GETDATE() THEN 'Expired' ELSE 'Not Taken' END)
		--Comment by karthikeyan.R 
		--IF(@TypeOfOT=1)
				--	BEGIN
				--		SELECT 
				--			@SumOfOTMins = (@SumOfOTMins + OtMins),
				--			@MaxDate = (CASE WHEN @SumOfOTMins >= @FullDayOTMins THEN [Date] WHEN @SumOfOTMins >= @HalfDayOTMins THEN [Date] ELSE NULL END),
				--			@OtText = (CASE WHEN ISNULL(@MaxDate, '') = '' THEN @OtText ELSE (@OtText + ';' + CAST(Process_Rec_ID AS VARCHAR(50)) + '&' + CAST((CASE WHEN @SumOfOTMins >= @FullDayOTMins THEN @FullDayOTMins WHEN @SumOfOTMins >= @HalfDayOTMins THEN @HalfDayOTMins ELSE @SumOfOTMins END) AS VARCHAR(10))) END),
				--			@SumOfOTMins = (CASE WHEN ISNULL(@MaxDate, '') = '' THEN @SumOfOTMins ELSE (CASE WHEN @SumOfOTMins >= @FullDayOTMins THEN 0 WHEN @SumOfOTMins >= @HalfDayOTMins THEN (@SumOfOTMins - @HalfDayOTMins) ELSE @SumOfOTMins END) END),
				--			@MaxDate = NULL
				--		FROM TA_Tbl_ProcessedData
				--			WHERE ([Date] BETWEEN @FromDate AND @ToDate) AND Employee_Code_Id = @Employee_code_Id 
				--			AND (DATEADD(D, @ValidDates, [Date])) >= @Date AND OtMins > 0 AND ISNULL(OtMins, '') != ''
				--		ORDER BY [Date]
				--	END
			--UPDATE @TblRecords SET 
		--	DateToExpired = CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved')
		--	AND TR.CompOffEligible = 1 THEN DATEADD(D,ISNULL(SS.VaildityCompOffDays, 0), CONVERT(datetime,TR.[Date],103)) END,
		--	NoOfDays =	CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved') AND TR.CompOffEligible = 1 THEN	
		--									(	CASE	WHEN TR.ExtraMins / DATEDIFF(MI,'00:00' ,SS.FullDayCompOff) > 0 THEN '1.0' 
		--													WHEN TR.ExtraMins/ DATEDIFF(MI,'00:00' ,SS.HalfDayCompOff) > 0 THEN '0.5' 
		--										END
		--									) 
		--							END,
		--	RemainingDays = CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved') AND TR.CompOffEligible = 1 THEN (CASE WHEN TR.ExtraMins / DATEDIFF(MI,'00:00' ,SS.FullDayCompOff) > 0 THEN '1.0' WHEN TR.ExtraMins/ DATEDIFF(MI,'00:00' ,SS.HalfDayCompOff) > 0 THEN '0.5' END) END,
		--	CompOffSatus = CASE WHEN (TR.OverTimeStatus = 'Pending' or TR.OverTimeStatus = 'Forwarded' or TR.OverTimeStatus = 'Approved') AND TR.CompOffEligible = 1 THEN (CASE WHEN DATEADD(D,ISNULL(SS.VaildityCompOffDays, 0),CONVERT(datetime,TR.[Date],103)) < GETDATE() THEN 'Expired' ELSE 'Not Taken' END) END
		--FROM @TblRecords TR
		--INNER JOIN TA_Tbl_ProcessedData PD ON PD.Process_Rec_ID = TR.Process_RecID
		--LEFT JOIN TA_Tbl_ShiftSettings SS ON SS.CompanyId = TR.TopLevelId
		--select * from @TblRecords
		INSERT INTO TA_Tbl_OverTimeStatus(Process_RecID, Employee_Id,ApplicationDate, [Date], HrsExtra, ExtraMins, OverTimeStatus, CompOffEligible, OTEligible, DateToExpired, NoOfDays, RemainingDays, CompOffSatus,isRequest,DaysWorked,InTime, OutTime,Reason,isDelete)
			SELECT Process_RecID, Employee_Id,CONVERT(datetime,ApplicationDate,103),CONVERT(datetime,[Date],103), 
			HrsExtra, ExtraMins, OverTimeStatus, CompOffEligible, OTEligible, 
			DateToExpired, NoOfDays, RemainingDays, CompOffSatus,1,DaysWorked,InTime,OutTime,Reason,0 FROM @TblRecords 
			--select * from @TblRecords
		SELECT '1' 
	END	 
	else
	begin
		SELECT '0'    
	END
END


-- Script 14 --
GO
/****** Object:  StoredProcedure [dbo].[Proc_TA_CompensationListing]    Script Date: 05-12-2025 14:44:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================  
-- Author:  Karthikeyan.R
-- Create date: 08-Feb-2017
-- Description: <Description,,>  
-- Proc_TA_CompensationListing null,null,'01/01/1900','01/01/1900','Management',0,3
-- select * from TA_Tbl_OverTimeStatus
 -- =============================================  
ALTER PROCEDURE [dbo].[Proc_TA_CompensationListing]  
 -- Add the parameters for the stored procedure here  
 @EmployeeFirstName nvarchar(MAX),  
 @EmployeeId nvarchar(MAX),  
 @fromdate nvarchar(40),  
 @ToDate nvarchar(40),
 @UserGroupName nvarchar(250),
 @ParentID int,
 @EmpAutoID int
AS  
BEGIN
	SET NOCOUNT ON;  
	Declare @sqlQuery nvarchar(Max)  
	create table #LevelRights(LevelId int)
	insert into  #LevelRights select * from [dbo].[fn_TA_GetParentChild](@UserGroupName,@ParentID) as LevelId	
	create table #EmpLevel(EmployeeId int,LevelId int,EffectiveDate datetime,Employee_Code_Id varchar(50),SwipeCard varchar(50))
	DECLARE @dateFormat as int
	DECLARE @TimeFormat as nvarchar(10)
	Select @dateFormat=DateFormat from Func_TA_GetDateTimeFormat()
	Select @TimeFormat=TimeFormat from Func_TA_GetDateTimeFormat()
	declare @isReportingto as int
	select @isReportingto=ReportsBasedOn from TA_Tbl_Usergroup where groupName=@UserGroupName
	create table #EmployeeIds(EmpAutoId int)
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_ReportingTo](@EmpAutoID) as EmpAutoId  
    insert into  #EmployeeIds select * from [dbo].[fn_TA_GetMappedEmployees_SancAuthority](@EmpAutoID) as EmpAutoId
	CREATE NONCLUSTERED INDEX [IDX_Effective] ON [dbo].[#EmpLevel] 
	(
		[EffectiveDate],[LevelId],[EmployeeId] ASC
	) ON [PRIMARY]
	declare @ELTodate as datetime
	if(@ToDate='01/01/1900')
	begin
		set @ELTodate=getdate()
	end
	else
	begin
		set @ELTodate=@ToDate
	end
	DECLARE @sqlEmpLevel nVARCHAR(4000) 
	 SET @sqlEmpLevel=
      'INSERT INTO #EmpLevel SELECT Elevel.EmployeeId,EL.levelId,Elevel.EffectiveDate,ELevel.Employee_Code_Id,ELevel.SwipeCardNo 
      from  (
      SELECT      max(el.EffectiveDate) as EffectiveDate,   el.EmployeeId as EmployeeId,emp.Employee_Code_Id,emp.SwipeCardNo 
      from TA_Tbl_EmployeeLevel el
      INNER JOIN ta_tbl_employee emp ON el.EmployeeId = emp.Employee_Id 
      INNER JOIN #LevelRights ON #LevelRights.LevelId=el.LevelId '
      set @sqlEmpLevel= @sqlEmpLevel +' WHERE emp.Isdeleted=0 group by el.EmployeeId,Employee_Code_Id,emp.SwipeCardNo) ELevel
      INNER JOIN TA_Tbl_EmployeeLevel EL ON EL.EffectiveDate=ELevel.EffectiveDate and EL.employeeid=ELevel.EmployeeId
      INNER JOIN #LevelRights ON #LevelRights.LevelId=EL.LevelId
      AND EL.EffectiveDate <= CONVERT(DATETIME,'''+cast(@ELTodate as nVARCHAR(50))+''', 102) and EL.LevelId is not null'      
	EXECUTE(@sqlEmpLevel)
	--select * from #LevelRights
	--select * from #EmpLevel
	set @sqlQuery=''  
	set @sqlQuery='select  OT.ExtraTime_Id ''Compensation_Id'',E.Employee_Code_Id as ''Employee_ID'',E.Employee_FirstName + '' ''+ isnull(E.Employee_LastName,'''') as Employee_FirstName ,
	case when '+CONVERT(varchar,@dateFormat)+' =106 then
		REPLACE(CONVERT(VARCHAR, OT.ApplicationDate,'+CONVERT(varchar,@dateFormat)+'),'' '',''-'') 
	else CONVERT(VARCHAR, OT.ApplicationDate, '+CONVERT(varchar,@dateFormat)+') end as ApplicationDate,
	case when '+CONVERT(varchar,@dateFormat)+' =106 then
		REPLACE(CONVERT(VARCHAR, OT.Date,'+CONVERT(varchar,@dateFormat) + '),'' '',''-'') 
	else CONVERT(VARCHAR, OT.Date, '+CONVERT(varchar,@dateFormat)+') end as FromDate,'''' Type,OT.Reason,OT.OverTimeStatus ''Status'',
	case	when OT.InTime<>''00:00'' then	case	when ('''+CONVERT(varchar,@TimeFormat)+ ''') =''hh:mm'' then 
	CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,OT.InTime,0)), 114)
															else SUBSTRING(OT.InTime,13,5)+'' '' + SUBSTRING(OT.InTime,18,2)
													end 
						else ''''
				end	InTime
	,
	case	when OT.OutTime<>''00:00'' then	case	when ('''+CONVERT(varchar,@TimeFormat)+ ''') =''hh:mm'' then CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,OT.OutTime,0)), 114)
															else	SUBSTRING(OT.OutTime,13,5)+'' '' + SUBSTRING(OT.OutTime,18,2) 
													end 
						else ''''
				end	OutTime
,OT.HrsExtra ''WorkedHours'',OT.DaysWorked ''DayWorked'',OT.OverTimeStatus ''Comp_Status'',Ta_Tbl_levelDetails.name as LevelName
	from TA_Tbl_OverTimeStatus OT inner join TA_Tbl_Employee E on OT.Employee_Id=E.Employee_Id  '
	set @sqlQuery = @sqlQuery + ' Inner join #EmpLevel on #EmpLevel.employeeid=E.Employee_Id '
	set @sqlQuery = @sqlQuery + ' inner join Ta_Tbl_levelDetails on #EmpLevel.LevelId = Ta_Tbl_levelDetails.id'
	set @sqlQuery = @sqlQuery + ' Inner join #LevelRights on #LevelRights.LevelId=#EmpLevel.LevelId where OT.isRequest=1 and OT.isDelete=0 and OT.OTEligible=0 and OT.CompOffEligible=1'
	if(@EmployeeFirstName<>'')  
		Begin
			set @sqlQuery = @sqlQuery + ' and (E.Employee_FirstName +'' '' + isnull(E.Employee_LastName,'''') like ''' + cast(@EmployeeFirstName+'%' as nvarchar(100)) + ''')'
		End
	if(@EmployeeId<> '')  
		Begin
			set @sqlQuery = @sqlQuery + ' and e.Employee_Code_Id = ''' + cast(@EmployeeId as nvarchar(50)) + ''''
		End
	if(@isReportingto > 0)
            BEGIN
               set @sqlQuery = @sqlQuery + ' and e.Employee_ID IN (select * from #EmployeeIds) '  
            END
	If not(@fromdate = '01/01/1900' and @ToDate = '01/01/1900')
		begin
			set @sqlQuery= @sqlQuery + ' and OT.Date>=CONVERT(DATETIME,'''+cast(@fromdate as nvarchar(50))+''',102) '  
			set @sqlQuery= @sqlQuery + ' and OT.Date<=CONVERT(DATETIME,'''+cast(@ToDate as nvarchar(50))+''',102) '  
		end
	set @sqlQuery= @sqlQuery + ' order by OT.ExtraTime_Id desc'
	--print @sqlQuery
	exec(@sqlQuery)
	drop table #LevelRights
	drop table #EmpLevel
END

-- Script 15--
ALTER PROCEDURE [dbo].[Proc_Ta_GetEmployeeShiftDateDetails]
@date nvarchar(20),
@EmployeeCode nvarchar(20),
@Tag nvarchar(10)=Null
AS
BEGIN
	SET NOCOUNT ON;
	DECLARE @TIMEFORMAT AS NVARCHAR(10)
	DECLARE @dateFormat as int	
	declare @EmployeeID as int
	declare @LeaveStatus as nvarchar(100)
	select @DATEFORMAT=DateFormat,@TIMEFORMAT=TimeFormat from [dbo].[Func_TA_GetDateTimeFormat]()
	declare @GetLevelID as int
	select @GetLevelID=EL.LevelId,@EmployeeID=E.Employee_Id from TA_Tbl_EmployeeLevel EL inner join TA_Tbl_Employee E on E.Employee_Id=EL.EmployeeId where E.Employee_Code_Id=@EmployeeCode
    DECLARE @childID INT 
    ;WITH ParentLevel AS
    (
		SELECT *, 1 AS Lvl FROM Ta_Tbl_levelDetails WHERE id = @GetLevelID
        UNION ALL
        SELECT rh.*, Lvl+1 AS Lvl FROM Ta_Tbl_levelDetails rh
        INNER JOIN ParentLevel rc ON rh.id = rc.ParentId
     )
     SELECT TOP 1 @GetLevelID=id FROM ParentLevel r ORDER BY lvl DESC
     declare @CountRecord as int
     SELECT @CountRecord = count(Employee_Id) FROM TA_Tbl_Employee 
		WHERE Leave_Sanc_Authority = Reporting_To AND Employee_Id = @EmployeeID  
	declare @OTApproval as int
	select @OTApproval=OTApproval from TA_Tbl_ApprovalSettings where CompanyId=@GetLevelID
	DECLARE @HalfDayOTMins BIGINT, @FullDayOTMins BIGINT, @TypeOfOT BIGINT,@OtText VARCHAR(MAX)
			SELECT		@TypeOfOT = OtHourWise,
						@HalfDayOTMins = DATEDIFF(MI,'00:00', ISNULL(OtMinHrs, '00:00')),
						@FullDayOTMins = DATEDIFF(MI,'00:00',ISNULL(FullDayOTMinHrs, '00:00'))
						FROM TA_Tbl_LeavePolicy LP INNER JOIN TA_Tbl_Emp_LeavePolicy ELP ON ELP.LeavePolicy_Id = LP.Policy_Id
						WHERE ELP.EmployeeId = @EmployeeID AND ELP.ToDate = (SELECT TOP 1 ELP.ToDate FROM TA_Tbl_Emp_LeavePolicy ELP
						INNER JOIN TA_Tbl_YearSetting YS ON YS.YearSettings_Id = ELP.YearSettingId
						WHERE YS.IsActive = 1 AND ELP.EmployeeId = @EmployeeID ORDER BY ELP.ToDate DESC)


	IF(@CountRecord > 0)
	BEGIN
		SET @LeaveStatus = 'Forwarded'
	END
	ELSE IF(@CountRecord = 0)
	BEGIN
		SET @LeaveStatus = 'Pending'
	END	
	if(isnull(@OTApproval,0)=0)
	begin
		SET @LeaveStatus = 'Approved'
	end

	print @CountRecord
	print @LeaveStatus

	Select p.Employee_code_id,p.Date,p.Shift_id,
	case when OutTime is not null and OutTime<>'00:00' then
		CASE	WHEN (@TIMEFORMAT)='HH:MM' THEN  CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,p.OutTime,0)), 114) 
				 ELSE substring(convert(varchar(25),convert(datetime,OutTime)),13,5)+' '+ substring(convert(varchar(25),convert(datetime,OutTime)),18,2)
		END 
	end OutTime,
	case when InTime is not null and InTime<>'00:00' then
		CASE	WHEN (@TIMEFORMAT)='HH:MM' THEN CONVERT(VARCHAR(8),DATEADD(MINUTE,0,CONVERT(DATETIME,p.InTime,0)), 114) 
				ELSE substring(convert(varchar(25),convert(datetime,InTime)),13,5)+' '+ substring(convert(varchar(25),convert(datetime,InTime)),18,2)
		END
	end
	InTime,
	p.NormOt,
	p.OffOt,p.HolOt,E.Employee_FirstName + case when E.Employee_MiddleName is not null then ' ' + cast(E.Employee_MiddleName as varchar(50)) else ' ' end + E.Employee_LastName AS 'Employee Name',
	Case when @tag='Compoff' then case WHEN p.OtMins >= @HalfDayOTMins THEN 'Reached' else 'Not Reached' end Else Case when p.OtMins >=(Select [dbo].[ConvertHoursToMinites](Shift_MinTimeOT)  From TA_tbl_Shift where Shift_id=p.shift_id) 
	then 'Reached' Else 'Not Reached'  End End as ComStatus,
	CASE when (p.NormOt!='00:00') then 'Week Day' ELSE
	CASE when (p.OffOt!='00:00') then 'Weekly Off' ELSE 
	CASE when (p.HolOt!='00:00') then 'Holiday' ELSE
	'NO OT Record Found' END END END  as  'WeekofftypeNormOt',@GetLevelID 'TopLevelID'
	,E.Employee_Id
	,LD.name as LevelName
	,p.ExtraMins
	,@LeaveStatus as 'OverTimeStatus',p.Process_Rec_ID,
	case when isnull(@HalfDayOTMins,0)=0 then 'LeavePolicyNotAssign' else 'Assign' end as LeavePolicyStatus
	from Ta_tbl_Processeddata p 
	inner join TA_Tbl_Employee E on p.Employee_Code_Id=E.SwipeCardNo
	inner join TA_Tbl_EmployeeLevel EL on EL.EmployeeId=E.Employee_Id
	inner join Ta_Tbl_levelDetails LD on LD.ID=EL.LevelId where  date = CONVERT(DATETIME,cast(@date as nvarchar(50)), 102) 
	and CompOff_Eligible=1 and OT_Eligible=1 and E.Employee_Code_Id = @EmployeeCode
	--and p.FNAttendanceCode in ('Weekly Off', 'Holiday') and p.ANAttendanceCode in ('Weekly Off', 'Holiday') --for vaighai client  
	--Select E.Employee_FirstName + case when E.Employee_MiddleName is not null then ' ' + cast(E.Employee_MiddleName as varchar(50)) else ' ' end + E.Employee_LastName AS 'Employee Name' from Ta_Tbl_employee Where Employee_code_id=@EmployeeCode
END



